Skip site navigation (1) Skip section navigation (2)

multi column foreign key for implicitly unique columns

From: Markus Bertheau <twanger(at)bluetwanger(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: multi column foreign key for implicitly unique columns
Date: 2004-08-17 09:25:24
Message-ID: 1092734724.2627.4.camel@dicaprio.akademie1.de (view raw or flat)
Thread:
Lists: pgsql-sql
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"

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?

Thanks

-- 
Markus Bertheau <twanger(at)bluetwanger(dot)de>


Responses

pgsql-sql by date

Next:From: Oliver ElphickDate: 2004-08-17 09:39:36
Subject: Re: multi column foreign key for implicitly unique columns
Previous:From: Theo GalanakisDate: 2004-08-17 02:47:12
Subject: Re: CROSS-TAB query help? I have read it cant be done in on

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group