update语句与外键约束冲突(update statement conflicted with foreign key constraint)

update语句与外键约束冲突(update statement conflicted with foreign key constraint)

我在将列集更新为主键时遇到问题，即使我在更新集默认情况下也是如此

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

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.

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

谢谢

I have problem on updating column set as primary key, even I made on update set default

this is my code for creating tables, I set on update set default

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

after that, I inserted data to the tables

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

but, when I run this code to update the column id

update department set id = 44 where id = 4

I get that error

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.

but I do not know where is my fault!

thanks

最满意答案

在外键约束中使用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也会更新以跟随更改的值。

Use ON UPDATE CASCADE in the foreign key constraint, like so:

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

If there is any update to the id column value, dept_id also gets updated to follow the changed value.