From: Oliver Elphick <olly@lfix.co.uk>
To: 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 10:39:36 +0100
Message-ID: <1092735576.28365.48.camel@linda> (raw)
In-Reply-To: <1092734724.2627.4.camel@dicaprio.akademie1.de>
References: <1092734724.2627.4.camel@dicaprio.akademie1.de>
On Tue, 2004-08-17 at 10:25, 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.
> Example:
>
> CREATE TABLE p (
> name TEXT PRIMARY KEY,
> "type" TEXT
> );
>
> CREATE TABLE f (
> name TEXT,
> "type" TEXT,
> FOREIGN KEY(name, "type") REFERENCES p(name, "type")
> );
> ERROR: there is no unique constraint matching given keys for referenced table "p"
What's the point of this? p.name is the primary key and is therefore
unique in p, so your foreign key should simply reference p.name. Having
f.type as a repetition of p.type violates normalisation principles,
since name is completely derivable by a join of f to p on name.
> 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?
If there is no unique key, how does the foreign key trigger find the
referenced row except by doing a sequential scan? Bad news! And when
one of the duplicate referenced rows changes, what should happen with ON
UPDATE or ON DELETE?
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"If ye abide in me, and my words abide in you, ye shall
ask what ye will, and it shall be done unto you."
John 15:7
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: olly@lfix.co.uk, twanger@bluetwanger.de
Subject: Re: multi column foreign key for implicitly unique columns
In-Reply-To: <1092735576.28365.48.camel@linda>
* 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