Re: simulating partial fkeys.. [ATTN Developers please] - Mailing list pgsql-sql

From Richard Huxton
Subject Re: simulating partial fkeys.. [ATTN Developers please]
Date
Msg-id 200306061142.36867.dev@archonet.com
Whole thread Raw
In response to simulating partial fkeys..  (<mallah@trade-india.com>)
Responses Re: simulating partial fkeys.. [ATTN Developers please]
List pgsql-sql
On Thursday 05 Jun 2003 3:55 pm, mallah@trade-india.com wrote:
> hi ,
>
> Is there any way to enforce fkeys only on subset of
> the table something on the lines of unique partial indexes

I'm afraid not (AFAIK). What might be a solution in your case is to define
another unique index. For example, you might have (row_id) as your primary
key with another unique index on (row_id,row_type). You could then have a
foreign-key that referenced those two columns.

> or any work around ? (on update or insert trigger is the only thing i can
> think of)

Yep, you'll need to build some triggers of your own. The techdocs guides
section is down at the moment, but see my brief example in the attachment.

It would be really useful to be able to have something like:

CREATE contract ( con_id  SERIAL, con_type varchar(4), ... PRIMARY KEY (con_id)
);

CREATE tel_con_section ( tcs_id  SERIAL, tcs_con_ref int4, ... CONSTRAINT contract_fk FOREIGN KEY (tcs_con_ref,'TEL')
REFERENCEScontract  
(con_id,con_type)
);

or even: FOREIGN KEY (tcs_con_ref) REFERENCES contract (con_id) WHERE
contract.con_type='TEL'

Is there a developer around who could comment how plausible this would be?

--  Richard Huxton

pgsql-sql by date:

Previous
From: Jan Wieck
Date:
Subject: Re: simulating partial fkeys..
Next
From: Joerg Hessdoerfer
Date:
Subject: Re: To ListAdms: Is pgsql-sql operating?