Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : SQL Server Management Studio Shortcuts
Error Messages
Home > SQL Server Error Messages > Msg 547 - DELETE statement conflicted with COLUMN REFERENCE constraint Constraint Name.  The conflict occurred in database Database Name, table Table Name, column Column Name.
SQL Server Error Messages - Msg 547 - DELETE statement conflicted with COLUMN REFERENCE constraint Constraint Name.  The conflict occurred in database Database Name, table Table Name, column Column Name.

Error Message

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE 
constraint Constraint Name.  The conflict occurred in
database Database Name, table Table Name, column
Column Name.
The statement has been terminated.

Causes:

This error occurs if you are trying to delete a record from a table that has a PRIMARY KEY and the record being deleted is being referenced as a FOREIGN KEY in another table.

To illustrate, assuming that in your Loans System, you have two tables, a table containing the different loan types accepted by the system ([dbo].[Loan Type]) and a table containing the loan applications ([dbo].[Loan Application]). The Loan Type ID in the Loan Application table references the Loan Type ID in the Loan Type table.

CREATE TABLE [dbo].[Loan Type] (
[Loan Type ID]	VARCHAR(20) NOT NULL PRIMARY KEY,
[Name]		VARCHAR(50) NOT NULL
)
GO

CREATE TABLE [dbo].[Loan Application] (
    [Loan ID]        INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    [Loan Type ID]   VARCHAR(20)  NOT NULL
                     REFERENCES [dbo].[Loan Type] ( [Loan Type ID] ),
    [Borrower]       VARCHAR(100) NOT NULL
)
GO

Here’s some sample records from the 2 tables:

[dbo].[Loan Type]
Loan Type ID  Name
------------- ------------------
CAR           Car Loan
HOME          Home Loan
HOME EQUITY   Home Equity Loan
PERSONAL      Personal Loan
STUDENT       Student Loan

[dbo].[Loan Application]
Loan ID  Loan Type ID  Borrower             
-------- ------------- -------------------- 
1        HOME          Old MacDonald
2        HOME          Three Little Pigs
3        CAR           Cinderella
4        STUDENT       Peter Pan

Due to changes in business requirements, you may be asked to delete the Student Loan from the available loan types accepted by the company.

DELETE FROM [dbo].[Loan Type]
WHERE [Loan Type ID] = 'STUDENT'
GO

But since there’s an existing record in the Loan Application table that references the Student Loan loan type, you get the following error:

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Loan Appl_Loan'.
The conflict occurred in database 'TestDb', table 'Loan Application', column 'Loan Type ID'.
The statement has been terminated.

Solution / Work Around:

One way to avoid this error is to first delete all records from the other tables that reference the PRIMARY KEY.

DELETE FROM [dbo].[Loan Application]
WHERE [Loan Type ID] = ‘STUDENT’
GO

DELETE FROM [dbo].[Loan Type]
WHERE [Loan Type ID] = ‘STUDENT’
GO

But deleting records from other tables may not be acceptable because these records may still be needed. An alternative to the physical deletion of the record is the implementation of a logical deletion of the record.  This can be done by adding a new column in the table that will determine if the record is still active or not.  A bit column can serve as a status flag wherein a value of 1 means that the record is still active while a value of 0 means that the record is not used anymore.

ALTER TABLE [dbo].[Loan Type]
ADD [Status] BIT NOT NULL DEFAULT (1)
GO

UPDATE [dbo].[Loan Type]
SET [Status] = 0
WHERE [Loan Type ID] = ‘STUDENT’
GO

Loan Type ID  Name               Status
------------- ------------------ ------
CAR           Car Loan           1
HOME          Home Loan          1
HOME EQUITY   Home Equity Loan   1
PERSONAL      Personal Loan      1
STUDENT       Student Loan       0
Related Articles :