pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedavoid circular references
3+ messages / 2 participants
[nested] [flat]
* avoid circular references
@ 2003-11-05 21:40 Jerome Alet <alet@librelogiciel.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jerome Alet @ 2003-11-05 21:40 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: avoid circular references
@ 2003-11-06 05:15 Josh Berkus <josh@agliodbs.com>
parent: Jerome Alet <alet@librelogiciel.com>
0 siblings, 1 reply; 3+ messages in thread
From: Josh Berkus @ 2003-11-06 05:15 UTC (permalink / raw)
To: Jerome Alet <alet@librelogiciel.com>; pgsql-sql
Jerome,
> Each object can contain one or many other objects from the same
> table, so I guess this relationship can be expressed with something
> like :
This is called a "tree structure".
> CREATE TABLE contains (id SERIAL PRIMARY KEY NOT NULL,
> containerid INTEGER REFERENCES objtable(idobj),
> contentid INTEGER REFERENCES objtable(idobj) );
This is overcomplicating things, unless a single object can belong to multiple
containers, which would strike me as peculiar. Easier just to have a
"containerid" in the objtable, which is your basic Proximity List.
But of course, maybe I'm not understanding you and you do have multiple
inheritance.
> 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.
Best to use some kind of recursive function. I do this for a calendaring
setup with event templates, where events can have multiple (possible) parents
and multiple children. Just write a pl/pgSQL function which reverse-traces
the parentage of the new object, looking for copies of itself.
> 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 ?
A trigger. Just use a BEFORE trigger and raise an exception if a self-parent
is found.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: avoid circular references
@ 2003-11-06 06:18 Jerome Alet <alet@librelogiciel.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 3+ messages in thread
From: Jerome Alet @ 2003-11-06 06:18 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql
On Wed, Nov 05, 2003 at 09:15:39PM -0800, Josh Berkus wrote:
> Jerome,
>
> > Each object can contain one or many other objects from the same
> > table, so I guess this relationship can be expressed with something
> > like :
>
> This is called a "tree structure".
Thanks !
> > CREATE TABLE contains (id SERIAL PRIMARY KEY NOT NULL,
> > containerid INTEGER REFERENCES objtable(idobj),
> > contentid INTEGER REFERENCES objtable(idobj) );
>
> This is overcomplicating things, unless a single object can belong to multiple
> containers, which would strike me as peculiar. Easier just to have a
> "containerid" in the objtable, which is your basic Proximity List.
>
> But of course, maybe I'm not understanding you and you do have multiple
> inheritance.
Yes an object could theorically belong to multiple containers, so
that's not really a tree I suppose.
> > 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.
>
> Best to use some kind of recursive function. I do this for a calendaring
> setup with event templates, where events can have multiple (possible) parents
> and multiple children. Just write a pl/pgSQL function which reverse-traces
> the parentage of the new object, looking for copies of itself.
Any example for this, especially on how to raise an integrity
exception ?
> > 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 ?
>
> A trigger. Just use a BEFORE trigger and raise an exception if a self-parent
> is found.
Thanks for your help !
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-11-06 06:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-05 21:40 avoid circular references Jerome Alet <alet@librelogiciel.com>
2003-11-06 05:15 ` Josh Berkus <josh@agliodbs.com>
2003-11-06 06:18 ` Jerome Alet <alet@librelogiciel.com>
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