Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6PFMgf41000 for ; Wed, 25 Jul 2001 11:22:42 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 91198; Wed, 25 Jul 2001 08:22:31 -0700 From: "Josh Berkus" Subject: Re: Meta integrity To: Renato De Giovanni , pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Wed, 25 Jul 2001 08:22:31 -0700 Message-ID: In-Reply-To: <3B5EE103.BCB37659@viafractal.com.br> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="_===91198====davinci.ethosmedia.com===_" X-Archive-Number: 200107/341 X-Sequence-Number: 4154 This is a multi-part MIME message --_===91198====davinci.ethosmedia.com===_ Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit 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 --_===91198====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===91198====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===91198====davinci.ethosmedia.com===_ Content-Type: text/plain Content-Disposition: attachment; filename="" Content-Transfer-Encoding: base64 DQo= --_===91198====davinci.ethosmedia.com===_--