Received: from C9Mailgw06.prontomail.com ([216.163.188.10]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6PEw5f10312 for ; Wed, 25 Jul 2001 10:58:06 -0400 (EDT) (envelope-from rdg@viafractal.com.br) Received: by C9Mailgw06.prontomail.com (NPlex 5.5.029) id 3B58A3940012F467 for pgsql-sql@postgresql.org; Wed, 25 Jul 2001 07:52:44 -0700 Received: from 200.201.140.64 by SmtpServer for ; Wed, 25 Jul 2001 14:52:33 +0000 Message-ID: <3B5EE103.BCB37659@viafractal.com.br> Date: Wed, 25 Jul 2001 12:08:51 -0300 From: Renato De Giovanni X-Mailer: Mozilla 4.72 [en] (Win95; I) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Meta integrity Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200107/339 X-Sequence-Number: 4152 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@viafractal.com.br