pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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







view thread (5+ messages)  latest in thread

Message-ID: <web-91198@davinci.ethosmedia.com>
Permalink:  ../web-91198@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-91198@davinci.ethosmedia.com

 · 

reply

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