X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id A89385E46C7
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Aug 2004 06:50:17 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 90912-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Aug 2004 09:50:09 +0000 (GMT)
Received: from web11.manitu.net (web11.manitu.net [217.11.48.111])
	by svr1.postgresql.org (Postfix) with ESMTP id 4D79C5E46CA
	for <pgsql-sql@postgresql.org>; Tue, 17 Aug 2004 06:50:06 -0300 (ADT)
Received: from [62.165.4.166] (dicaprio.akademie1.de [62.165.4.166])
	(authenticated)
	by web11.manitu.net (8.10.2-SOL3/8.10.2) with ESMTP id i7H9nvg17184;
	Tue, 17 Aug 2004 11:49:57 +0200
Subject: Re: multi column foreign key for implicitly unique columns
From: Markus Bertheau <twanger@bluetwanger.de>
To: olly@lfix.co.uk
Cc: pgsql-sql@postgresql.org
In-Reply-To: <1092735576.28365.48.camel@linda>
References: <1092734724.2627.4.camel@dicaprio.akademie1.de>
	<1092735576.28365.48.camel@linda>
Content-Type: text/plain; charset=UTF-8
Message-Id: <1092736197.2627.12.camel@dicaprio.akademie1.de>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) 
Date: Tue, 17 Aug 2004 11:49:57 +0200
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200408/157
X-Sequence-Number: 18654

=D0=92 =D0=92=D1=82=D1=80, 17.08.2004, =D0=B2 11:39, Oliver Elphick =D0=BF=
=D0=B8=D1=88=D0=B5=D1=82:

> 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.

The real situation is a little more complicated:

CREATE TABLE classes (
    name TEXT PRIMARY KEY
);
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20
CREATE TABLE class_fields (
    class_name TEXT REFERENCES classes(name),
    field_name TEXT,
    PRIMARY KEY(class_name, field_name)
);
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20
CREATE TABLE objects (
    name TEXT PRIMARY KEY,
    class_name TEXT REFERENCES classes(name)
);
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20
CREATE TABLE object_versions (
    object_name TEXT REFERENCES objects(name),
    object_version DATE,
    PRIMARY KEY(object_name, object_version)
);
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20
CREATE TABLE object_version_property_values (
    object_name TEXT REFERENCES objects(name),
    object_version DATE,
    class_name TEXT,
    field_name TEXT,
    value TEXT,
    FOREIGN KEY(object_name, object_version)
	REFERENCES object_versions(object_name, object_version),
    -- this fk is needed to make sure that the the object in
    -- question really is of the class that field_name is a field of
    FOREIGN KEY(object_name, class_name)
	REFERENCES objects(name, class_name),
    FOREIGN KEY(class_name, field_name)
	REFERENCES class_fields(class_name, field_name)
);
ERROR:  there is no unique constraint matching given keys
	for referenced table "objects"

I need the fk on the columns.

--=20
Markus Bertheau <twanger@bluetwanger.de>