pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMeta integrity
5+ messages / 5 participants
[nested] [flat]
* Meta integrity
@ 2001-07-25 15:08 Renato De Giovanni <rdg@viafractal.com.br>
0 siblings, 3 replies; 5+ messages in thread
From: Renato De Giovanni @ 2001-07-25 15:08 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Meta integrity
@ 2001-07-25 15:22 Josh Berkus <josh@agliodbs.com>
parent: Renato De Giovanni <rdg@viafractal.com.br>
2 siblings, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2001-07-25 15:22 UTC (permalink / raw)
To: Renato De Giovanni <rdg@viafractal.com.br>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Meta integrity
@ 2001-07-25 16:26 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Renato De Giovanni <rdg@viafractal.com.br>
2 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2001-07-25 16:26 UTC (permalink / raw)
To: Renato De Giovanni <rdg@viafractal.com.br>; +Cc: pgsql-sql
On Wed, 25 Jul 2001, Renato De Giovanni wrote:
> 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)
> );
Well, if you don't mind the extra space (and a bit of cheating), this
might work (untested):
add an attribute to specific_attribute class_id default 'X' and a check
constraint to prevent it from ever being something else and a unique
constraint on (id,class_id) to object (meaningless since id is already
unique, but necessary for following the letter of the spec), and then do a
foreign key (object_id, class_id) references object(id, class_id) in
specific_attribute.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Meta integrity
@ 2001-07-25 17:49 Grigoriy G. Vovk <grigoriy.vovk@linustech.com.cy>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 5+ messages in thread
From: Grigoriy G. Vovk @ 2001-07-25 17:49 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Renato De Giovanni <rdg@viafractal.com.br>; pgsql-sql
Yes, its not a task for _relation_ dbms.
I am database developer, I like rdbms, but now I think that I should start
to use LDAP for these kind of tasks.
What people can say?
Jul 25, 08:22 -0700, Josh Berkus wrote:
> 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
>
my best regards,
----------------
Grigoriy G. Vovk
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Meta integrity
@ 2001-07-27 05:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Renato De Giovanni <rdg@viafractal.com.br>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2001-07-27 05:20 UTC (permalink / raw)
To: Renato De Giovanni <rdg@viafractal.com.br>; +Cc: pgsql-sql
Renato De Giovanni <rdg@viafractal.com.br> writes:
> FOREIGN KEY (object_id, 'X') REFERENCES object (id, class_id)
Why not just store the class_id in the secondary table (if you're
concerned about space, consider using an int4 to represent class_id).
Then you can do a direct two-column foreign key constraint, plus add
a check constraint like CHECK(class_id = 'X').
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-07-27 05:20 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-25 15:08 Meta integrity Renato De Giovanni <rdg@viafractal.com.br>
2001-07-25 15:22 ` Josh Berkus <josh@agliodbs.com>
2001-07-25 17:49 ` Grigoriy G. Vovk <grigoriy.vovk@linustech.com.cy>
2001-07-25 16:26 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-07-27 05:20 ` Tom Lane <tgl@sss.pgh.pa.us>
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