Meta integrity
От | Renato De Giovanni |
---|---|
Тема | Meta integrity |
Дата | |
Msg-id | 3B5EE103.BCB37659@viafractal.com.br обсуждение исходный текст |
Ответы |
Re: Meta integrity
Re: Meta integrity Re: Meta integrity |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: