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 casesfield case_idfield case_namefield case_status

table statusfield statusfield relationfield 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