Re: simulating partial fkeys..

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