constraint deferred but fails?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

constraint deferred but fails?

Nagy László Zsolt

Hello,

I have deferred the only fk constraint to this table inside a
transaction, but I still cannot delete rows from it.

How is that possible?


master=> ALTER TABLE doc.display_mode disable trigger user;
ALTER TABLE
master=> BEGIN;
BEGIN
master=> SET CONSTRAINTS doc.fk_book_display_mode_id DEFERRED;
SET CONSTRAINTS
master=> DELETE FROM doc.display_mode;
ERROR:  update or delete on table "display_mode" violates foreign key
constraint "fk_book_display_mode_id" on table "book"
DETAIL:  Key (id)=(1) is still referenced from table "book".
master=>


Reply | Threaded
Open this post in threaded view
|

Re: constraint deferred but fails?

David G Johnston
On Wednesday, January 23, 2019, Nagy László Zsolt <[hidden email]> wrote:
master=> SET CONSTRAINTS doc.fk_book_display_mode_id DEFERRED;

The constraint itself has to be changed to deferrable in order for it to be affected by the above.

David J. 
 
Reply | Threaded
Open this post in threaded view
|

Re: constraint deferred but fails?

Nagy László Zsolt

It is already deferrable:

ALTER TABLE doc.book ADD CONSTRAINT fk_book_display_mode_id FOREIGN KEY (display_mode_id) REFERENCES doc.display_mode(id) DEFERRABLE


2019-01-24 07:39 keltezéssel, David G. Johnston írta:
On Wednesday, January 23, 2019, Nagy László Zsolt <[hidden email]> wrote:
master=> SET CONSTRAINTS doc.fk_book_display_mode_id DEFERRED;

The constraint itself has to be changed to deferrable in order for it to be affected by the above.

David J. 
 

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

Reply | Threaded
Open this post in threaded view
|

Re: constraint deferred but fails?

Ron-2
In reply to this post by Nagy László Zsolt
On 1/24/19 12:35 AM, Nagy László Zsolt wrote:
Hello,

I have deferred the only fk constraint to this table inside a
transaction, but I still cannot delete rows from it.

How is that possible?


master=> ALTER TABLE doc.display_mode disable trigger user;
ALTER TABLE
master=> BEGIN;
BEGIN
master=> SET CONSTRAINTS doc.fk_book_display_mode_id DEFERRED;
SET CONSTRAINTS
master=> DELETE FROM doc.display_mode;
ERROR:  update or delete on table "display_mode" violates foreign key
constraint "fk_book_display_mode_id" on table "book"
DETAIL:  Key (id)=(1) is still referenced from table "book".
master=>

Deferring a constraint doesn't mean "disappearing" the constraint; it just means... deferring it until later (i.e. commit time).  It's only purpose is to allow your code to delete from doc.display_mode before deleting from book .  But you do have to delete from book at some point in the transaction.

--
Angular momentum makes the world go 'round.
Reply | Threaded
Open this post in threaded view
|

Re: constraint deferred but fails?

David G Johnston
In reply to this post by Nagy László Zsolt
Please don’t top-post replies on these lists.

On Wednesday, January 23, 2019, Nagy László Zsolt <[hidden email]> wrote:

It is already deferrable:

ALTER TABLE doc.book ADD CONSTRAINT fk_book_display_mode_id FOREIGN KEY (display_mode_id) REFERENCES doc.display_mode(id) DEFERRABLE


Ignoring the question of why you want to do things this way it seems you need to provide a self-contained, complete, test case to get a sound answer.  Though I suspect it may have to do with an on delete clause you haven’t shown.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: constraint deferred but fails?

Shreeyansh dba
In reply to this post by Nagy László Zsolt
To automate this, you could define the foreign key constraint with ON DELETE CASCADE.
So when deleting first delete child rows and then parent.

Thanks & Regards,
Shreeyansh DBA Team
www.shreeyansh.com


On Thu, Jan 24, 2019 at 12:05 PM Nagy László Zsolt <[hidden email]> wrote:

Hello,

I have deferred the only fk constraint to this table inside a
transaction, but I still cannot delete rows from it.

How is that possible?


master=> ALTER TABLE doc.display_mode disable trigger user;
ALTER TABLE
master=> BEGIN;
BEGIN
master=> SET CONSTRAINTS doc.fk_book_display_mode_id DEFERRED;
SET CONSTRAINTS
master=> DELETE FROM doc.display_mode;
ERROR:  update or delete on table "display_mode" violates foreign key
constraint "fk_book_display_mode_id" on table "book"
DETAIL:  Key (id)=(1) is still referenced from table "book".
master=>