Re: multi column foreign key for implicitly unique columns

On Wed, 18 Aug 2004, Josh Berkus wrote:

> > In the case that a table constraint is a referential constraint,
> > the table is referred to as the referencing table. The referenced
> > columns of a referential constraint shall be the unique columns of
> > some unique constraint of the referenced table.
>
> Missed that one. Interesting. AFAIK, the uniqueness of referenced columns is
> NOT a requirement of Relaitonal Algebra. So why does SQL require it?
>
> Maybe I'll ask Joe Celko after he finishes moving to Austin.
>
> 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.

Well, I think SQL does give a way of specifying that constraint through
assertions and check constraints with subselects. We just don't support
either of those constructs.