问题

我有三个表,有这样的链依赖关系:

 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);
 

我想更新子表barbaz.但是,当在转换中更新它们时,我收到错误:

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

如何同时更新子表以避免外键约束错误?

  最佳答案

如果在每个外键上设置ON UPDATE CASCADE,关系的主键端的更改应该传播到外部的字段.它可以像这样设置:

 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);
 

然后您应该像这样更新bar.id,更改将自动发生在baz.id中.

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

  相同标签的其他问题

sqlsqlite