On Wed, 25 Jul 2001, Renato De Giovanni wrote:
> 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),
> FOREIGN KEY (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 INTEGER NOT 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 INTEGER NOT NULL,
> PRIMARY KEY (id),
> FOREIGN KEY (object_id, 'X') REFERENCES object (id, class_id)
> );
Well, if you don't mind the extra space (and a bit of cheating), this
might work (untested):
add an attribute to specific_attribute class_id default 'X' and a check
constraint to prevent it from ever being something else and a unique
constraint on (id,class_id) to object (meaningless since id is already
unique, but necessary for following the letter of the spec), and then do a
foreign key (object_id, class_id) references object(id, class_id) in
specific_attribute.
