Skip site navigation (1) Skip section navigation (2)

Re: multi column foreign key for implicitly unique columns

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 (view raw or flat)
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.

In response to

Responses

pgsql-sql by date

Next:From: Josh BerkusDate: 2004-08-18 19:36:14
Subject: Re: multi column foreign key for implicitly unique columns
Previous:From: Josh BerkusDate: 2004-08-18 18:55:34
Subject: Re: multi column foreign key for implicitly unique columns

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group