Re: multi column foreign key for implicitly unique columns

Josh Berkus wrote:
> I have my own issue that forced me to use triggers. Given:
>
> table users (
> name
> login PK
> status
> etc. )
>
> table status (
> status
> relation
> label
> definition
> PK status, relation )
>
> the relationship is:
> users.status = status.status AND status.relation = 'users';
>
> This is a mathematically definable constraint, but there is no way in standard
> SQL to create an FK for it. This is one of the places I point to whenever
> we have the "SQL is imperfectly relational" discussion.

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?

--
Richard Huxton
Archonet Ltd