Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Jan Wieck <JanWieck@Yahoo.com>
Subject: Re: multi column foreign key for implicitly unique columns
Date: Wed, 18 Aug 2004 10:05:13 -0700
User-Agent: KMail/1.4.3
Cc: Markus Bertheau <twanger@bluetwanger.de>, olly@lfix.co.uk,
	pgsql-sql@postgresql.org
References: <1092734724.2627.4.camel@dicaprio.akademie1.de>
	<200408171945.15663.josh@agliodbs.com> <41235A90.1080300@Yahoo.com>
In-Reply-To: <41235A90.1080300@Yahoo.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200408181005.13230.josh@agliodbs.com>

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