X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (developer.postgresql.org [64.117.224.193])
	by svr1.postgresql.org (Postfix) with ESMTP id 39A4AEFFBA
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  6 Jun 2003 15:59:27 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 34484-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  6 Jun 2003 15:59:25 +0000 (GMT)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by svr1.postgresql.org (Postfix) with ESMTP id 78CD6EFFA8
	for <pgsql-sql@postgresql.org>; Fri,  6 Jun 2003 15:59:25 +0000 (GMT)
Received: from [63.195.55.98] (HELO spooky)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 3097168; Fri, 06 Jun 2003 08:59:31 -0700
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: <mallah@trade-india.com>, <pgsql-sql@postgresql.org>
Subject: Re: simulating partial fkeys..
Date: Fri, 6 Jun 2003 08:58:03 -0700
User-Agent: KMail/1.4.3
References: <1153.219.65.253.151.1054824932.squirrel@mail.trade-india.com>
In-Reply-To: <1153.219.65.253.151.1054824932.squirrel@mail.trade-india.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200306060858.03349.josh@agliodbs.com>
X-Archive-Number: 200306/41
X-Sequence-Number: 13799

Mallah,

> Is there any way to enforce fkeys only on subset of
> the table something on the lines of unique partial indexes
>
> or any work around ? (on update or insert trigger is the only thing i can
> think of)

<grin> so, why don't you ask me these questions?   Nu?

If you mean what I believe that you mean ... let me give you an example from 
my own systems:

table cases
	field case_id
	field case_name
	field case_status

table status
	field status
	field relation
	field description

In this schema, the table "status" holds status values for all relations 
(tables), not just for "cases".   I would like to create an FK from 
case_status to status.status *only for those values of status where relation 
= 'cases'.

Can't be done. (at least, not with an FK declaration -- see below for a 
workaround)

This is a classic example of one of the failures of the SQL Standard.   The 
above relationship is easily definable in Relational Calculus, but SQL will 
not support it. And given PostgreSQL's commitment to that standard, we cannot 
really extend Postgres's FK implementation to cover that situation.

"distributed keys" is another really good example of a useful Relational 
structure that SQL will not support.

The only way to enforce this in the database would be to create triggers (not 
Rules, for performance reasons, since Rules can't use indexes) FOR INSERT, 
UPDATE ON cases, and FOR UPDATE, DELETE on status.   The triggers on status 
would be annoyingly long.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco