Re: multi column foreign key for implicitly unique columns

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: Markus Bertheau <twanger(at)bluetwanger(dot)de>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: multi column foreign key for implicitly unique columns
Date: 2004-08-17 09:39:36
Message-ID: 1092735576.28365.48.camel@linda
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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(at)lfix(dot)co(dot)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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Huxton 2004-08-17 09:48:37 Re: CROSS-TAB query help? I have read it cant be done in on
Previous Message Markus Bertheau 2004-08-17 09:25:24 multi column foreign key for implicitly unique columns