pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mans <mananvyas@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Circular Dependency in Tables and Deletion of Data
Date: 24 Jul 2006 05:53:52 -0700
Message-ID: <1153745632.239636.120300@h48g2000cwc.googlegroups.com> (raw)

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




view thread (4+ messages)  latest in thread

Message-ID: <1153745632.239636.120300@h48g2000cwc.googlegroups.com>
Permalink:  ../1153745632.239636.120300@h48g2000cwc.googlegroups.com/
Also on:    postgresql.org/message-id/1153745632.239636.120300@h48g2000cwc.googlegroups.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: mananvyas@gmail.com
  Subject: Re: Circular Dependency in Tables and Deletion of Data
  In-Reply-To: <1153745632.239636.120300@h48g2000cwc.googlegroups.com>
  References: <1153745632.239636.120300@h48g2000cwc.googlegroups.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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