X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 4FF5F9FA4C5 for ; Mon, 24 Jul 2006 13:21:07 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 30813-10 for ; Mon, 24 Jul 2006 13:21:00 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by postgresql.org (Postfix) with ESMTP id 7F90D9FA441 for ; Mon, 24 Jul 2006 13:21:00 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id A978C350E5; Mon, 24 Jul 2006 09:20:57 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id A779F350E3; Mon, 24 Jul 2006 09:20:57 -0700 (PDT) Date: Mon, 24 Jul 2006 09:20:57 -0700 (PDT) From: Stephan Szabo To: Mans Cc: pgsql-admin@postgresql.org Subject: Re: Circular Dependency in Tables and Deletion of Data In-Reply-To: <1153745632.239636.120300@h48g2000cwc.googlegroups.com> Message-ID: <20060724091649.H55713@megazone.bigpanda.com> References: <1153745632.239636.120300@h48g2000cwc.googlegroups.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.113 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200607/191 X-Sequence-Number: 22437 On Mon, 24 Jul 2006, Mans wrote: > Hello, > > Note: I know that its not a very good db design. But what if a senario > comes in front of you? > > I have created an uncommon database design. Which is as follows: > Created 2 Tables :- Emp1 and Emp2 > > TableA with 2 Attributes > A1 and A2. > A1 is the primary key. > > TableB with 2 Attributes > B1 and B22. > B1 is primary key > B2 is refereing to Emp1.Pr1 > > Now I have altered table for addeing one more foreign key constraint. > The constraint is on TableA.A2 refering to TableB.B1 > > This creates the circular dependency. > Now I have add one record in each table then "Will it allow me to > delete without schema change??"If yes then how? > I can not delete the tables also?? > Is this a useless functionality given in DB or where is this used? > Can postgres detect it automatically and restrict it to do so? > Is any other DB restricts this kind of functionality or not? > > Sample code is given below for trial purpose.... > > create table TableA ( > A1 integer primary key, > A2 integer > ); > > create table TableB ( > B1 integer primary key, > B2 integer references TableA(A1) > ); > > ALTER TABLE TableA ADD CONSTRAINT distfk FOREIGN KEY (A2) REFERENCES > TableB(B1) MATCH FULL; You need to have one or both of the constraints marked at least as deferrable. If you make the constraint initially deferred, then it should automatically wait until transaction end to check that constraint (thus you could use a second statement in the same transaction to remove the related rows). If you leave it as initially immediate, then you can use SET CONSTRAINTS to make it temporarily deferred. Also, the schema you gave should not prevent you from removing rows whose referencing column is NULL, which is potentially useful for some people.