Unable to delete a row from a table due to a dependency/reference in another table

Question

I have a stored procedure that deletes a record from a table where one of the columns matches a specified value:

ALTER PROCEDURE [dbo].[Sp_DelBranch] @datafield varchar(50)
AS
BEGIN
    DELETE FROM Branch WHERE branchname = @datafield     
END

Unfortunately I get the following error on execution:

The DELETE statement conflicted with the REFERENCE constraint "fk_BranchIdDept". The conflict occurred in database "MproWorkSpace", table "dbo.Department", column 'BranchId'.

Can anyone explain why I'm seeing this error?

The error occurs in your stored procedure. Please add that code. — juergen d, Apr 24 '14 at 11:15

score 1 · Answer 1 · edited May 23 '17 at 11:49

1

conflicted with the REFERENCE constraint "fk_BranchIdDept".

This means, that the value you are trying to delete, is primary key in some other table and is referenced in this table as foreign key,i.e., a primary-foreign key relation is mapped through constraint fk_BranchIdDept ...So unless you delete the referred primary key, foreign key can not be removed from table.

Otherwise, this will lead to data inconsistency!

Look for Cascade Delete to help you in this!
SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?

edited May 23 '17 at 11:49

Community

1
1

answered Apr 24 '14 at 11:19

NoobEditor

15,563
19
81
112

1

How do you delete the PK, without deleting the FK? I think you got it the other way round – Raj Apr 24 '14 at 11:21
The structure is like - WorldBank is a group and WorldBank Italy is the branch. The branch has its dependency on WorldBank. I just want to delete the branch in italy . – Smoking monkey Apr 24 '14 at 11:23
The table of branches is having the field GroupID as the FK. – Smoking monkey Apr 24 '14 at 11:26
this community sure is awesome in terms of answering...glad we could help!! :D – NoobEditor Apr 24 '14 at 11:52

score 0 · Answer 2 · answered Apr 24 '14 at 11:25

The error means that there are some rows in Department table referencing the branch you're trying to delete.

You should either delete the corresponding rows from the Department table first, or change values in BranchId column for these rows, so they point to some other branch.

score 0 · Accepted Answer · edited May 23 '17 at 12:05

0

The DELETE statement conflicted with the REFERENCE constraint "fk_BranchIdDept".

The record you're trying to delete is referenced in other tables; deleting the Branch record would leave the other tables orphaned, and referencing something that no longer exists.

There are two approaches to this:

Delete all records in referenced tables before deleting from the Branch table
Perform a cascade delete (deleting from the Branch table triggers a delete in every referenced table)

edited May 23 '17 at 12:05

Community

1
1

answered Apr 24 '14 at 11:28

Kevin Hogg

1,771
25
34

You got me right. Very well explained the solution. Thanks a lot . – Smoking monkey Apr 24 '14 at 11:51
How to see all the constraints which I made earlier ?. I didn't care about naming them but now I want to apply the cascade delete on all of them . Whenever error comes only then I get to know about their name. – Smoking monkey Apr 24 '14 at 12:09
In `SQL Server Management Studio`, in the `Object Explorer`, right-click the table `dbo.Branch` and select `View Dependencies`. The default view `Objects that depend on [Branch]` will show all tables, stored procedures, etc. that depend on the Branch table. – Kevin Hogg Apr 24 '14 at 12:21

Unable to delete a row from a table due to a dependency/reference in another table

3 Answers3