Re: Initially Deffered - FK

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Denis <sqllist(at)coralindia(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Initially Deffered - FK
Date: 2004-01-16 05:18:32
Message-ID: 20040115211617.C53092@megazone.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


On Fri, 16 Jan 2004, Denis wrote:
> create table contact (id int constraint contact_pk primary key, name
> text );
> create table address (id int constraint address_fk references contact(id) on
> delete cascade initially deferred,
> city text,
> pin text);
>
> Lets.. insert few data in it..
>
> insert into contact values (1, 'Denis');
> insert into contact values (2, 'Anand');
> insert into contact values (3, 'Debatosh');
> insert into contact values (4, 'Pradeep');
>
> insert into address values (1,'Howrah','711102');
> insert into address values (2,'Kolkata','700001');
> insert into address values (3,'Jadavpur','700005');
> insert into address values (4,'Mumbai','400002');
>
> Now, below gives me the correct result.
>
> select * from contact; select * from address;
>
> acedg=> select * from contact; select * from address;
> id | name
> ----+----------
> 1 | Denis
> 2 | Anand
> 3 | Debatosh
> 4 | Pradeep
> (4 rows)
>
> id | city | pin
> ----+----------+--------
> 1 | Howrah | 711102
> 2 | Kolkata | 700001
> 3 | Jadavpur | 700005
> 4 | Mumbai | 400002
> (4 rows)
>
> BUT, the problem starts when i issue the following set of DMLs in
> transaction:
>
> begin;
> delete from contact where id=1;
> insert into contact values (1, 'Denis');
> delete from address where id=1; /* this is not required.. but my
> app.fires. Should not have any impact */
> insert into address values (1,'Howrah','711102');
> end;
>
> It gives me the result:
>
> acedg=> select * from contact; select * from address;
> id | name
> ----+----------
> 2 | Anand
> 3 | Debatosh
> 4 | Pradeep
> 1 | Denis
> (4 rows)
>
> id | city | pin
> ----+----------+--------
> 2 | Kolkata | 700001
> 3 | Jadavpur | 700005
> 4 | Mumbai | 400002
> (3 rows)
>
> Where is my lastly inserted row ?? i.e.
> insert into address values (1,'Howrah','711102');

Definitional difference. We currently treat a
request to defer the constraint to mean defer
referential actions as well, thus the inserted
address is removed when the on delete cascade
occurs after it at transaction end. Noone's
been entirely sure whether this is correct
or not per spec as I remember.

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message denis 2004-01-16 05:31:13 Re: Initially Deffered - FK
Previous Message Denis 2004-01-16 04:34:18 Initially Deffered - FK