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