From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | Jan Wieck <JanWieck(at)Yahoo(dot)com>, Markus Bertheau <twanger(at)bluetwanger(dot)de>, olly(at)lfix(dot)co(dot)uk, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: multi column foreign key for implicitly unique columns |
Date: | 2004-08-18 19:24:40 |
Message-ID: | 20040818192440.GB21979@wolff.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Wed, Aug 18, 2004 at 10:05:13 -0700,
Josh Berkus <josh(at)agliodbs(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2004-08-18 19:36:14 | Re: multi column foreign key for implicitly unique columns |
Previous Message | Josh Berkus | 2004-08-18 18:55:34 | Re: multi column foreign key for implicitly unique columns |