X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6A83DD1BB54 for ; Thu, 6 Nov 2003 05:17:28 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 08104-01 for ; Thu, 6 Nov 2003 01:16:57 -0400 (AST) Received: from davinci.ethosmedia.com (server228.ethosmedia.com [209.128.84.228]) by svr1.postgresql.org (Postfix) with ESMTP id 3A9EBD1B579 for ; Thu, 6 Nov 2003 01:16:55 -0400 (AST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3875629; Wed, 05 Nov 2003 21:17:37 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Jerome Alet , pgsql-sql@postgresql.org Subject: Re: avoid circular references Date: Wed, 5 Nov 2003 21:15:39 -0800 User-Agent: KMail/1.4.3 References: <20031105214041.GC13937@mail.librelogiciel.com> In-Reply-To: <20031105214041.GC13937@mail.librelogiciel.com> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200311052115.39330.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/33 X-Sequence-Number: 15848 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