Re: multi column foreign key for implicitly unique columns

On Wed, Aug 18, 2004 at 10:05:13 -0700, Josh Berkus <josh@agliodbs.com> 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.    

If users is supposed to reference status you can do this by adding a relation
column to users, using a constraint to force relation to always be 'users'
and then having (status, relation) being a foreign key.