CODEWITHSUNDEEP
sql-serversql-server-2012
sarahHH
sarahHH

Reputation: 191

update statement conflicted with foreign key constraint

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

Upvotes: 3

Views: 17333

Answers (4)

Bartho Bernsmann
Bartho Bernsmann

Reputation: 2493

If you don't want to change your table structure, you can run the following query: 

ALTER TABLE [UserStore] 
NOCHECK CONSTRAINT FK_UserStore_User_UserId

ALTER TABLE [UserIdentity]
NOCHECK CONSTRAINT  FK_UserIdentity_User_UserId

BEGIN TRAN

UPDATE  [user] 
SET Id = 10
WHERE Id = 9

UPDATE  [dbo].[UserStore]
SET UserId = 10
WHERE UserId = 9

UPDATE  [dbo].UserIdentity
SET UserId = 10
WHERE UserId = 9

COMMIT TRAN

ALTER TABLE [UserStore] 
CHECK CONSTRAINT FK_UserStore_User_UserId

ALTER TABLE UserIdentity 
CHECK CONSTRAINT FK_UserIdentity_User_UserId

Upvotes: 1

NoName
NoName

Reputation: 124

it's normally to that error to appear, You have a foreign key relation and you then change the value.

Use ON UPDATE CASCADE in the foreign key constraint

Upvotes: 1

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

Because employee table have dept_id column *constraint* with id column of department. This error is encountered when the primary key of a table is updated but it is referenced by a foreign key from another table and the update specific is set to No action. The No action is the default option.

If this is your case and No action is set on the update operation you can change the foreign-key definition to Cascade

You can try this:

ALTER TABLE employee
DROP Constraint FK__employee__dept_i__571DF1D5
GO

ALTER TABLE employee
ADD CONSTRAINT New_FK_Constraint
FOREIGN KEY (dept_id) REFERENCES department (id)
ON DELETE CASCADE ON UPDATE CASCADE
GO

Upvotes: 1

shree.pat18
shree.pat18

Reputation: 21757

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.

Upvotes: 2

Related Questions