Re: Meta integrity

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(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco