constraint deferred but fails?
constraint deferred but fails?
От:
Nagy László Zsolt <gandalf@shopzeus.com>
Дата:
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=>
Re: constraint deferred but fails?
От:
"David G. Johnston" <david.g.johnston@gmail.com>
Дата:
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.
Re: constraint deferred but fails?
От:
Nagy László Zsolt <gandalf@shopzeus.com>
Дата:
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.
Re: constraint deferred but fails?
От:
Ron <ronljohnsonjr@gmail.com>
Дата:
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.
Re: constraint deferred but fails?
От:
"David G. Johnston" <david.g.johnston@gmail.com>
Дата:
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.
Re: constraint deferred but fails?
От:
Shreeyansh Dba <shreeyansh2014@gmail.com>
Дата:
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=>