From: Jerome Alet <alet@librelogiciel.com>
To: pgsql-sql@postgresql.org
Subject: avoid circular references
Date: Wed, 5 Nov 2003 22:40:41 +0100
Message-ID: <20031105214041.GC13937@mail.librelogiciel.com> (raw)
Hi there,
I've got a table 'objtable' of objects which primary id is 'idobj' of type
serial.
objtable :
idobj objname
1 nameone
2 nametwo
3 namethree
...
Each object can contain one or many other objects from the same
table, so I guess this relationship can be expressed with something
like :
CREATE TABLE contains (id SERIAL PRIMARY KEY NOT NULL,
containerid INTEGER REFERENCES objtable(idobj),
contentid INTEGER REFERENCES objtable(idobj) );
What I would like is to be able to avoid circular references :
- an object can't contain itself.
- an object can't contain one of its containers.
So I'd like to know how to create additionnal integrity constraints
to solve this problem, and especially what syntax I should use.
I thought about creating a rule but maybe the best is a trigger
(I'm not sure I really understand the difference) if I see
what happens when I create new tables. But what can I do in
my trigger to have PostgreSQL understand there's an integrity
violation ?
Thanks in advance for any help on this.
Jerome Alet
--
"A non-free program is a predatory social system that keeps people
in a state of domination and division, and uses the spoils to
dominate more." - RMS
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: alet@librelogiciel.com
Subject: Re: avoid circular references
In-Reply-To: <20031105214041.GC13937@mail.librelogiciel.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