pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Circular Dependency in Tables and Deletion of Data
4+ messages / 3 participants
[nested] [flat]

* Circular Dependency in Tables and Deletion of Data
@ 2006-07-24 12:53 Mans <mananvyas@gmail.com>
  2006-07-24 13:08 ` Re: Circular Dependency in Tables and Deletion of Data Peter Eisentraut <peter_e@gmx.net>
  2006-07-24 16:20 ` Re: Circular Dependency in Tables and Deletion of Data Stephan Szabo <sszabo@megazone.bigpanda.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Mans @ 2006-07-24 12:53 UTC (permalink / raw)
  To: pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Circular Dependency in Tables and Deletion of Data
  2006-07-24 12:53 Circular Dependency in Tables and Deletion of Data Mans <mananvyas@gmail.com>
@ 2006-07-24 13:08 ` Peter Eisentraut <peter_e@gmx.net>
  2006-07-24 13:20   ` Re: Circular Dependency in Tables and Deletion of Data Mans <mananvyas@gmail.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Peter Eisentraut @ 2006-07-24 13:08 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Mans <mananvyas@gmail.com>

Am Montag, 24. Juli 2006 14:53 schrieb Mans:
> Isnt it a crazy thing??

Yes, but with deferrable constraints and transactions blocks, it should be no 
problem to manage the data.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Circular Dependency in Tables and Deletion of Data
  2006-07-24 12:53 Circular Dependency in Tables and Deletion of Data Mans <mananvyas@gmail.com>
  2006-07-24 13:08 ` Re: Circular Dependency in Tables and Deletion of Data Peter Eisentraut <peter_e@gmx.net>
@ 2006-07-24 13:20   ` Mans <mananvyas@gmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Mans @ 2006-07-24 13:20 UTC (permalink / raw)
  To: pgsql-admin

Hi Peter,

Can you please give some detailed information about it?

Regards,
Mans

Peter Eisentraut wrote:

> Am Montag, 24. Juli 2006 14:53 schrieb Mans:
> > Isnt it a crazy thing??
>
> Yes, but with deferrable constraints and transactions blocks, it should be no
> problem to manage the data.
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Circular Dependency in Tables and Deletion of Data
  2006-07-24 12:53 Circular Dependency in Tables and Deletion of Data Mans <mananvyas@gmail.com>
@ 2006-07-24 16:20 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Stephan Szabo @ 2006-07-24 16:20 UTC (permalink / raw)
  To: Mans <mananvyas@gmail.com>; +Cc: pgsql-admin

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.



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2006-07-24 16:20 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-24 12:53 Circular Dependency in Tables and Deletion of Data Mans <mananvyas@gmail.com>
2006-07-24 13:08 ` Peter Eisentraut <peter_e@gmx.net>
2006-07-24 13:20   ` Mans <mananvyas@gmail.com>
2006-07-24 16:20 ` Stephan Szabo <sszabo@megazone.bigpanda.com>

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