Thread: constraint deferred but fails?
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=>
On Wednesday, January 23, 2019, Nagy László Zsolt <gandalf@shopzeus.com> 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.
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 <gandalf@shopzeus.com> 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.
On 1/24/19 12:35 AM, Nagy László Zsolt wrote:
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.
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.
Angular momentum makes the world go 'round.
Please don’t top-post replies on these lists.
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.
To automate this, you could define the foreign key constraint with ON DELETE CASCADE.
So when deleting first delete child rows and then parent.
On Thu, Jan 24, 2019 at 12:05 PM Nagy László Zsolt <gandalf@shopzeus.com> 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=>