Saturday, 5 July 2014

The DELETE statement conflicted with the REFERENCE constraint

You will get this error when you are trying to Delete the record from a Table which has a reference in another Table. Consider two tables Customers(Primary Table) and SalesHistory(Relative Table).
Fix: The DELETE statement conflicted with the REFERENCE constraint
Here the column CustomerID is Primary Key of the table Customers and which is referenced as Foreign Key column in SalesHistoryTable. IDREFERENCE constraint is "FK__SalesHist__Custo__060DEAE8".

Fix: The DELETE statement conflicted with the REFERENCE constraint

When we try to delete a row from Customers Table with CustomerID which is referenced in SalesHistory.
Delete
  FROM [MorganDB].[dbo].[Customers] where CustomerID=2
We will get this SQL error:
Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the REFERENCE constraint "FK__SalesHist__Custo__060DEAE8". 
The conflict occurred in database "MorganDB", table "dbo.SalesHistory", column 'CustomerID'.
To solve this issue, we need to delete corresponding rows from SalesHistory Table first and then delete from Table Customers.(it means, we need to delete dependency first before deleting actual data)
Delete
  FROM [MorganDB].[dbo].[SalesHistory] where CustomerID=2
Delete
  FROM [MorganDB].[dbo].[Customers] where CustomerID=2

Advertisements
Advertisements

No comments:

Post a Comment