From: Markus Bertheau <twanger@bluetwanger.de>
To: olly@lfix.co.uk
Cc: pgsql-sql@postgresql.org
Subject: Re: multi column foreign key for implicitly unique columns
Date: Tue, 17 Aug 2004 11:49:57 +0200
Message-ID: <1092736197.2627.12.camel@dicaprio.akademie1.de> (raw)
In-Reply-To: <1092735576.28365.48.camel@linda>
References: <1092734724.2627.4.camel@dicaprio.akademie1.de>
<1092735576.28365.48.camel@linda>
В Втр, 17.08.2004, в 11:39, Oliver Elphick пишет:
> 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
);
CREATE TABLE class_fields (
class_name TEXT REFERENCES classes(name),
field_name TEXT,
PRIMARY KEY(class_name, field_name)
);
CREATE TABLE objects (
name TEXT PRIMARY KEY,
class_name TEXT REFERENCES classes(name)
);
CREATE TABLE object_versions (
object_name TEXT REFERENCES objects(name),
object_version DATE,
PRIMARY KEY(object_name, object_version)
);
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.
--
Markus Bertheau <twanger@bluetwanger.de>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: twanger@bluetwanger.de, olly@lfix.co.uk
Subject: Re: multi column foreign key for implicitly unique columns
In-Reply-To: <1092736197.2627.12.camel@dicaprio.akademie1.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox