Meta integrity

From: Renato De Giovanni <rdg(at)viafractal(dot)com(dot)br>
To: pgsql-sql(at)postgresql(dot)org
Subject: Meta integrity
Date: 2001-07-25 15:08:51
Message-ID: 3B5EE103.BCB37659@viafractal.com.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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),
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)
);

Thanks in advance,
--
Renato
Sao Paulo - SP - Brasil
rdg(at)viafractal(dot)com(dot)br

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Josh Berkus 2001-07-25 15:14:09 Re: Re: Inserts in triggers Follow Up
Previous Message Henshall, Stuart - WCP 2001-07-25 13:13:32 Re: how can we get total records in pg server?