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