Обсуждение: avoid circular references
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 INTEGERREFERENCES 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
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
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