Re: multi column foreign key for implicitly unique columns

Jan,

> 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.

--
Josh Berkus
Aglio Database Solutions
San Francisco