sql-server - 更新语句与外键约束冲突

Question

我在更新设置为主键的列时遇到问题，即使我在更新集默认设置上也是如此

这是我创建表的代码，我设置了更新集默认值

create table department(
id int default 10 primary key,
name varchar(50)
);

create table employee
(
id int primary key,
dept_id int default 40 foreign key references department(id) on update set default on delete set default,
    name varchar(40)
);

之后，我向表中插入数据

insert into department
values
(1,'hr'),
(2,'programming'),
(3,'telesales'),
(4,'database')

insert into employee
values
(1,1,'mohammed'),
(2,2,'magd'),
(3,1,'soha'),
(4,3,'sameh'),
(5,4,'ashraf')

但是，当我运行这段代码来更新列 id 时

update department
set id = 44 where id = 4

我得到了那个错误

The UPDATE statement conflicted with the FOREIGN KEY constraint "FK__employee__dept_i__571DF1D5". The conflict occurred in database "test", table "dbo.department", column 'id'.
The statement has been terminated.

但我不知道我的错在哪里!

谢谢

Answer 1

在外键约束中使用ON UPDATE CASCADE，像这样:

    create table employee
(
id int primary key,
dept_id int default 40 foreign key references department(id) on update cascade on delete set default,
    name varchar(40)
);

如果 id 列值有任何更新，dept_id 也会更新以遵循更改后的值。