pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Renato De Giovanni <rdg@viafractal.com.br>
To: pgsql-sql@postgresql.org
Subject: Meta integrity
Date: Wed, 25 Jul 2001 12:08:51 -0300
Message-ID: <3B5EE103.BCB37659@viafractal.com.br> (raw)

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





view thread (5+ messages)  latest in thread

Message-ID: <3B5EE103.BCB37659@viafractal.com.br>
Permalink:  ../3B5EE103.BCB37659@viafractal.com.br/
Also on:    postgresql.org/message-id/3B5EE103.BCB37659@viafractal.com.br

 · 

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: rdg@viafractal.com.br
  Subject: Re: Meta integrity
  In-Reply-To: <3B5EE103.BCB37659@viafractal.com.br>

* 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