"FOREIGN KEY constraint failed" when updating dependent tables during a transaction

I have three tables with a chain dependencies like this:

pragma foreign_keys = ON;
create table foo (id integer primary key);
create table bar (id integer primary key references foo(id));
create table baz (id integer primary key references bar(id));
insert into foo values (1), (2);
insert into bar values (1);
insert into baz values (1);

I want to update the child tables bar and baz. However, when updating them within a transation, I get an error:

begin;
update bar set id = 2 where id = 1;
update baz set id = 2 where id = 1;
commit;
-- Error: FOREIGN KEY constraint failed

How can I update the child table simultaneously to avoid the foreign key constraint error?


If you set ON UPDATE CASCADE on each foreign key, the changes in the primary key side of the relationship should be propagated to the field on the foreign side. It can be set like this:

create table foo (id integer primary key);
create table bar (id integer primary key references foo(id) on update cascade);
create table baz (id integer primary key references bar(id) on update cascade);

Then you should just need to update bar.id like so, and the change will occur automatically in baz.id.

begin;
update bar set id = 2 where id = 1;
commit;


Sometimes I wish I could read the docs more carefully.

Each foreign key constraint in SQLite is classified as either immediate or deferred. Foreign key constraints are immediate by default.(...)

If a statement modifies the contents of the database so that an immediate foreign key constraint is in violation at the conclusion the statement, an exception is thrown and the effects of the statement are reverted. By contrast, if a statement modifies the contents of the database such that a deferred foreign key constraint is violated, the violation is not reported immediately. Deferred foreign key constraints are not checked until the transaction tries to COMMIT.

So a table can declare a foreign key as DEFERRABLE INITIALLY DEFERRED to allow this kind of update.

pragma foreign_keys = ON;
create table foo (id integer primary key);
create table bar (id integer primary key references foo(id) deferrable initially deferred);
create table baz (id integer primary key references bar(id) deferrable initially deferred);
insert into foo values (1), (2);
insert into bar values (1);
insert into baz values (1);

begin;
update bar set id = 2 where id = 1;
update baz set id = 2 where id = 1;
commit;

But as sqlite does not easily allow to alter a table, it is nice to know that it is possible to obtain this behavior on existing tables that have immediate foreign keys with the defer_foreign_keys pragma:

pragma foreign_keys = ON;
create table foo (id integer primary key);
create table bar (id integer primary key references foo(id));
create table baz (id integer primary key references bar(id));
insert into foo values (1), (2);
insert into bar values (1);
insert into baz values (1);

pragma defer_foreign_keys=ON;
begin;
update bar set id = 2 where id = 1;
update baz set id = 2 where id = 1;
commit;

I must say, I don't understand the use of immediate foreign keys, and I don't see why they should be the default -- except perhaps for performance reasons?


You're probably trying to avoid this... but...

begin;
delete baz where id = 1;
delete bar where id = 1;
insert into bar values (2);
insert into baz values (2);
commit;