Обсуждение: 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

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