Message-ID: <4125AE16.8070401@archonet.com>
Date: Fri, 20 Aug 2004 08:53:58 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 0.7 (X11/20040615)
MIME-Version: 1.0
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
References: <1092734724.2627.4.camel@dicaprio.akademie1.de>
	<200408171945.15663.josh@agliodbs.com> <41235A90.1080300@Yahoo.com>
	<200408181005.13230.josh@agliodbs.com>
In-Reply-To: <200408181005.13230.josh@agliodbs.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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

It'd be nice to say something like:

ALTER TABLE status ADD CONSTRAINT user_status_fk
FOREIGN KEY (status) WHERE relation = 'users'
REFERENCES users(status);

And the flip-side so you can have:

ALTER TABLE cheque_details ADD CONSTRAINT chq_trans_fk
FOREIGN KEY (trans_id)
REFERENCES transactions(trans_id) WHERE trans_type='CHQ';

Actually, since we can have a "unique index with where" this second form 
should be do-able shouldn't it?

-- 
   Richard Huxton
   Archonet Ltd