Re: multi column foreign key for implicitly unique columns - Mailing list pgsql-sql

В Втр, 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_nameTEXT,   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
reallyis 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 keysfor referenced table "objects"

I need the fk on the columns.

--
Markus Bertheau <twanger@bluetwanger.de>