From: Josh Berkus <josh@agliodbs.com>
To: mallah@trade-india.com
To: pgsql-sql@postgresql.org
Subject: Re: simulating partial fkeys..
Date: Fri, 6 Jun 2003 08:58:03 -0700
Message-ID: <200306060858.03349.josh@agliodbs.com> (raw)
In-Reply-To: <1153.219.65.253.151.1054824932.squirrel@mail.trade-india.com>
References: <1153.219.65.253.151.1054824932.squirrel@mail.trade-india.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, mallah@trade-india.com
Subject: Re: simulating partial fkeys..
In-Reply-To: <200306060858.03349.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox