Re: multi column foreign key for implicitly unique columns

В Втр, 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(at)bluetwanger(dot)de>