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

On Fri, 6 Jun 2003, Richard Huxton wrote:

> On Thursday 05 Jun 2003 3:55 pm, mallah(at)trade-india(dot)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.

As a side note, I think the attached doesn't entirely work yet as
something similar to a foreign key. You almost certainly need to deal
with deletes as well as inserts and updates and without some kind of
locking I think you're going to fall prey to concurrent transactions
violating the constraint (what happens if someone say inserts a
server_product at the same time someone else updates server). Neither of
those should be hard to add to it.

> 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') REFERENCES contract
> (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?

The former syntax is probably reasonable, the latter seems more
problematic. However, IMHO the right way to do this is for someone who
has the time and inclination ( not me ;) ) to look at supporting
subselects in CHECK constraints. This allows you to define whatever wacky
constraint logic you want and it should be done properly (including the
concurrency issues and such).