Ð ÐÑÑ, 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 <[EMAIL PROTECTED]>
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
