From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Richard Huxton <dev@archonet.com>
Cc: 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:04:13 -0700 (PDT)
Message-ID: <20040820073854.L89156@megazone.bigpanda.com> (raw)
In-Reply-To: <4125AE16.8070401@archonet.com>
References: <1092734724.2627.4.camel@dicaprio.akademie1.de>
<200408171945.15663.josh@agliodbs.com>
<41235A90.1080300@Yahoo.com>
<200408181005.13230.josh@agliodbs.com>
<4125AE16.8070401@archonet.com>
On Fri, 20 Aug 2004, Richard Huxton wrote:
> 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?
Maybe, but there are some issues about how the feature would be defined.
What is legal in those WHERE clauses?
Can it refer to columns of the other table?
Does the condition need to be immutable?
If not, can it contain subselects?
Can one use referentials actions on the constraint?
If so, which rule is used for the second if a row is updated from having
'CHQ' to something else? Is it update because that's the original
command, in which case things like update cascade will still error, or
is it delete because the row is disappearing from the table created with
the where clause?
SQL has assertions which would presumably be able to handle the general
constraints above which should have questions like this defined (and
doesn't have referential actions I believe). It might be better to
implement those if one was going to do it.
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: sszabo@megazone.bigpanda.com, 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: <20040820073854.L89156@megazone.bigpanda.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