From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Richard Huxton <dev@archonet.com>
Cc: Markus Bertheau <twanger@bluetwanger.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: multi column foreign key for implicitly unique columns
Date: Tue, 17 Aug 2004 07:26:56 -0700 (PDT)
Message-ID: <20040817072443.S60684@megazone.bigpanda.com> (raw)
In-Reply-To: <4121D55D.1040105@archonet.com>
References: <1092734724.2627.4.camel@dicaprio.akademie1.de>
<4121D55D.1040105@archonet.com>
On Tue, 17 Aug 2004, Richard Huxton wrote:
> Markus Bertheau wrote:
> > Hi,
> >
> > PostgreSQL doesn't allow the creation of a foreign key to a combination
> > of fields that has got no dedicated unique key but is unique nonetheless
> > because a subset of the combination of fields has a unique constraint.
> [snip example]
> > Is this on purpose? I think the foreign key should be allowed. Creating
> > an extra unique key only has a negative impact on performance, right?
>
> As you say, the uniqueness is guaranteed so there's no good reason why
> it couldn't be made to work. It's probably more of an implementation
> issue. Unique constraints are implemented with an index, so I'm guessing
No, actually, it's that the SQL92 (at least) spec says explicitly that
there must be a unique constraint across all of the columns specified, not
merely across a subset.
"then the set of column names of that <reference column list> shall be
equal to the set of column names in the unique columns of a unique
constraint of the referenced table."
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, twanger@bluetwanger.de
Subject: Re: multi column foreign key for implicitly unique columns
In-Reply-To: <20040817072443.S60684@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