I'm working on a project based on an unusual data model. Some entities
aren't represented by separate tables, they're grouped in the same table
just like the following simplified model shows:
CREATE TABLE class ( id CHAR(8) NOT NULL, name VARCHAR(30) NOT NULL, PRIMARY KEY (id)
);
INSERT INTO class VALUES ('X', 'Class x') ;
INSERT INTO class VALUES ('Y', 'Class y') ;
CREATE TABLE object ( id INTEGER NOT NULL, class_id CHAR(8) NOT NULL, PRIMARY KEY (id),
FOREIGNKEY (class_id) REFERENCES class (id)
);
INSERT INTO object VALUES (1, 'X') ;
INSERT INTO object VALUES (2, 'Y') ;
INSERT INTO object VALUES (3, 'X') ;
Now suppose we need to store in a separate table attributes from objects
from the specific class 'X'. Defining this table with...
CREATE TABLE specific_attribute ( id INTEGER NOT NULL, value TEXT NOT NULL, object_id
INTEGERNOT NULL, PRIMARY KEY (id), FOREIGN KEY (object_id) REFERENCES object (id)
);
...will only guarantee that each attribute points to an existent object
but it will not care about the object's class. Question is: how could I
also enforce this kind of "meta integrity"? The following table
definition came to my mind, but its an illegal construction:
CREATE TABLE specific_attribute ( id INTEGER NOT NULL, value TEXT NOT NULL, object_id
INTEGERNOT NULL, PRIMARY KEY (id), FOREIGN KEY (object_id, 'X') REFERENCES object (id, class_id)
);
Thanks in advance,
--
Renato
Sao Paulo - SP - Brasil
rdg@viafractal.com.br