Subject: Re: multi column foreign key for implicitly unique columns
From: Oliver Elphick <olly@lfix.co.uk>
Reply-To: olly@lfix.co.uk
To: Markus Bertheau <twanger@bluetwanger.de>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <1092734724.2627.4.camel@dicaprio.akademie1.de>
References: <1092734724.2627.4.camel@dicaprio.akademie1.de>
Content-Type: text/plain
Organization: LFIX Limited
Message-Id: <1092735576.28365.48.camel@linda>
Mime-Version: 1.0
Date: Tue, 17 Aug 2004 10:39:36 +0100
Content-Transfer-Encoding: 7bit

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