From: Richard Huxton <dev@archonet.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: Jan Wieck <JanWieck@Yahoo.com>
Cc: Markus Bertheau <twanger@bluetwanger.de>
Cc: olly@lfix.co.uk
Cc: pgsql-sql@postgresql.org
Subject: Re: multi column foreign key for implicitly unique columns
Date: Fri, 20 Aug 2004 08:53:58 +0100
Message-ID: <4125AE16.8070401@archonet.com> (raw)
In-Reply-To: <200408181005.13230.josh@agliodbs.com>
References: <1092734724.2627.4.camel@dicaprio.akademie1.de>
<200408171945.15663.josh@agliodbs.com>
<41235A90.1080300@Yahoo.com>
<200408181005.13230.josh@agliodbs.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, josh@agliodbs.com, JanWieck@Yahoo.com, twanger@bluetwanger.de, olly@lfix.co.uk
Subject: Re: multi column foreign key for implicitly unique columns
In-Reply-To: <4125AE16.8070401@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox