From: Josh Berkus <josh@agliodbs.com>
To: Renato De Giovanni <rdg@viafractal.com.br>
To: pgsql-sql@postgresql.org
Subject: Re: Meta integrity
Date: Wed, 25 Jul 2001 08:22:31 -0700
Message-ID: <web-91198@davinci.ethosmedia.com> (raw)
In-Reply-To: <3B5EE103.BCB37659@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, rdg@viafractal.com.br
Subject: Re: Meta integrity
In-Reply-To: <web-91198@davinci.ethosmedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox