Re: simulating partial fkeys.. [ATTN Developers please]

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