From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Denis <sqllist@coralindia.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Initially Deffered - FK
Date: Thu, 15 Jan 2004 21:18:32 -0800 (PST)
Message-ID: <20040115211617.C53092@megazone.bigpanda.com> (raw)
In-Reply-To: <006601c3dbea$02295800$0f32a8c0@denisnew>
References: <006601c3dbea$02295800$0f32a8c0@denisnew>
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.
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com, sqllist@coralindia.com
Subject: Re: Initially Deffered - FK
In-Reply-To: <20040115211617.C53092@megazone.bigpanda.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