Thread: Meta integrity
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
Renato,
> ...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)
> );
This is a fairly common problem that has no solution using REFERENCES,
either in Postgres or in SQL 99. You basically have two choices:
1. The rigorous -- write your own Triggers and Constraints to enforce
this kind of integrity, including INSERT, UPDATE, and DELETE triggers on
the various tables. Between postgresql.org and Roberto Mello's sight,
there's quite a bit of material on triggers.
2. The simple -- write functions to perform inserts, updates and deletes
on these tables. Put the relation into those functions, and make users
use those functions instead of direct SQL command access.
I took the second approach to solve a similar problem, because I had
quite a number of other business rules I needed to apply, and adding the
special relationship rule was only one more.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Attachment
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.
Yes, its not a task for _relation_ dbms. I am database developer, I like rdbms, but now I think that I should start to use LDAP for these kind of tasks. What people can say? Jul 25, 08:22 -0700, Josh Berkus wrote: > Renato, > > > ...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) > > ); > > This is a fairly common problem that has no solution using REFERENCES, > either in Postgres or in SQL 99. You basically have two choices: > > 1. The rigorous -- write your own Triggers and Constraints to enforce > this kind of integrity, including INSERT, UPDATE, and DELETE triggers on > the various tables. Between postgresql.org and Roberto Mello's sight, > there's quite a bit of material on triggers. > > 2. The simple -- write functions to perform inserts, updates and deletes > on these tables. Put the relation into those functions, and make users > use those functions instead of direct SQL command access. > > I took the second approach to solve a similar problem, because I had > quite a number of other business rules I needed to apply, and adding the > special relationship rule was only one more. > > -Josh > > > > ______AGLIO DATABASE SOLUTIONS___________________________ > Josh Berkus > Complete information technology josh@agliodbs.com > and data management solutions (415) 565-7293 > for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco > my best regards, ---------------- Grigoriy G. Vovk
Renato De Giovanni <rdg@viafractal.com.br> writes:
> FOREIGN KEY (object_id, 'X') REFERENCES object (id, class_id)
Why not just store the class_id in the secondary table (if you're
concerned about space, consider using an int4 to represent class_id).
Then you can do a direct two-column foreign key constraint, plus add
a check constraint like CHECK(class_id = 'X').
regards, tom lane