Hi all,
We've got an interesting case where we want deletes to cascade if one table was hit directly, but not another. We can show that the delete _would_ cascade from one foreign key relationship, but the delete is actually blocked by the foreign key constraint from the other relationship.
A sort of simplified view of the tables:
create table foo (
id integer primary key generated always as identity
);
create table bar (
id integer primary key generated always as identity
);
create table foo_bar (
foo_id integer not null,
bar_id integer not null,
primary key (foo_id, bar_id)
);
alter table foo_bar add constraint foo_bar_foo foreign key (foo_id) references foo(id) on delete cascade;
alter table foo_bar add constraint foo_bar_bar foreign key (bar_id) references bar(id);
create table baz (
id integer primary key generated always as identity,
foo_id integer not null
);
alter table baz add constraint baz_foo foreign key (foo_id) references foo(id) on delete cascade;
create table bazinga (
id integer primary key generated always as identity,
foo_id integer not null,
bar_id integer not null,
baz_id integer not null
);
alter table bazinga add constraint bazinga_foo_bar foreign key (foo_id, bar_id) references foo_bar (foo_id, bar_id);
alter table bazinga add constraint bazinga_baz foreign key (baz_id) references baz(id) on delete cascade;
What we wanted to happen:
delete from foo where id = 3;
-- cascades through the tree, deleting rows in bazinga through the baz_id relationship
delete from foo_bar where foo_id = 3 and bar_id = 1;
-- violates foreign key constraint bazinga_foo_bar
-- (this works as expected)
What actually happened:
delete from foo where id = 3;
-- violates foreign key constraint bazinga_foo_bar
How I've currently fixed it:
alter table bazinga add constraint bazinga_foo foreign key (foo_id) references foo(id) on delete cascade;
-- this foreign key relationship seems to be cascaded to earlier in the query
My questions:
What is the order of operations between cascading deletes and constraint checking? From what I can tell from the above, it seems like the delete cascades to each table in turn, and the constraints are checked at the time that the table is hit.
How do I know which table will be cascaded to first?
Is there a way to force the delete to cascade to tables in a specific order?
-Sam
https://github.com/nelsam"As an adolescent I aspired to lasting fame, I craved factual certainty, and
I thirsted for a meaningful vision of human life -- so I became a scientist.
This is like becoming an archbishop so you can meet girls."
-- Matt Cartmill
