Date: Wed, 18 Aug 2004 10:45:35 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: Jan Wieck <JanWieck@Yahoo.com>,
	Markus Bertheau <twanger@bluetwanger.de>, olly@lfix.co.uk,
	pgsql-sql@postgresql.org
Subject: Re: multi column foreign key for implicitly unique columns
In-Reply-To: <200408181005.13230.josh@agliodbs.com>
Message-ID: <20040818104151.Y6662@megazone.bigpanda.com>
References: <1092734724.2627.4.camel@dicaprio.akademie1.de>
	<200408171945.15663.josh@agliodbs.com> <41235A90.1080300@Yahoo.com>
	<200408181005.13230.josh@agliodbs.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Wed, 18 Aug 2004, Josh Berkus wrote:

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

Well, I think SQL does give a way of specifying that constraint through
assertions and check constraints with subselects.  We just don't support
either of those constructs.