Re: multi column foreign key for implicitly unique columns

On Fri, 20 Aug 2004, Richard Huxton wrote:

> It'd be nice to say something like:
>
> ALTER TABLE status ADD CONSTRAINT user_status_fk
> FOREIGN KEY (status) WHERE relation = 'users'
> REFERENCES users(status);
>
> And the flip-side so you can have:
>
> ALTER TABLE cheque_details ADD CONSTRAINT chq_trans_fk
> FOREIGN KEY (trans_id)
> REFERENCES transactions(trans_id) WHERE trans_type='CHQ';
>
> Actually, since we can have a "unique index with where" this second form
> should be do-able shouldn't it?

Maybe, but there are some issues about how the feature would be defined.

What is legal in those WHERE clauses?
Can it refer to columns of the other table?
Does the condition need to be immutable?
If not, can it contain subselects?
Can one use referentials actions on the constraint?
If so, which rule is used for the second if a row is updated from having
'CHQ' to something else? Is it update because that's the original
command, in which case things like update cascade will still error, or
is it delete because the row is disappearing from the table created with
the where clause?

SQL has assertions which would presumably be able to handle the general
constraints above which should have questions like this defined (and
doesn't have referential actions I believe). It might be better to
implement those if one was going to do it.