X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id E58BB9FA5BF for ; Mon, 24 Jul 2006 09:54:07 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 31145-04 for ; Mon, 24 Jul 2006 12:53:58 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from news.hub.org (news.hub.org [200.46.204.72]) by postgresql.org (Postfix) with ESMTP id 14C2F9FA50D for ; Mon, 24 Jul 2006 09:53:58 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id k6OCrotd055729 for ; Mon, 24 Jul 2006 09:53:50 -0300 (ADT) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id k6OCrnRB055724 for pgsql-admin@postgresql.org; Mon, 24 Jul 2006 09:53:49 -0300 (ADT) (envelope-from news) From: "Mans" X-Newsgroups: pgsql.admin Subject: Circular Dependency in Tables and Deletion of Data Date: 24 Jul 2006 05:53:52 -0700 Organization: http://groups.google.com Lines: 54 Message-ID: <1153745632.239636.120300@h48g2000cwc.googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Complaints-To: groups-abuse@google.com User-Agent: G2/0.2 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.0.4) Gecko/20060508 Firefox/1.5.0.4,gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: h48g2000cwc.googlegroups.com; posting-host=202.144.106.189; posting-account=73-Bxw0AAABBlm6w3KYQJAYF0wlVuY95 To: pgsql-admin@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200607/187 X-Sequence-Number: 22433 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; Insert INTO TableA values (1,null); Insert INTO TableB values (100,1); Update TableA SET A2 = 100 WHERE A1 = 1; Delete FROM TableA; Isnt it a crazy thing?? Regards, Mans