|
|
Error Message
Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with COLUMN REFERENCE
constraint Foreign Key Constraint Name. The conflict
occurred in database Database Name, table Table Name,
column Column Name.
The statement has been terminated.
|
Causes:
This error is encountered when the primary key of a table is updated and the primary is being referenced by a foreign key from another table and cascade update is set to NO ACTION, which is the default if not specified.
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 primary key 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
Assuming that you want to differentiate between a New Car Loan and a Pre-Owned/Used Car Loan and you want to change the Loan Type ID for the current loan type from the value of ‘CAR’ to ‘NEW CAR’ using the following UPDATE statement:
UPDATE [dbo].[Loan Type]
SET [Loan Type ID] = 'NEW CAR'
WHERE [Loan Type ID] = 'CAR'
Since the foreign key constraint did not specify the action to be performed when an update on the primary key is performed, by default the action is NO ACTION. Given this, when a change in the primary key value is performed on the Loan Type table using the UPDATE statement above, the following error is generated:
Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with COLUMN REFERENCE constraint 'FK_Loan_Appl_Loan_Type'.
The conflict occurred in database 'TestDB', table 'Loan Application', column 'Loan Type ID'.
The statement has been terminated.
Solution / Work Around:
To avoid this error from happening when the primary key value is updated on a table, make sure to specify CASCADE as the action to be performed on an update of the primary key during the creation of the table:
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] )
ON UPDATE CASCADE,
[Borrower] VARCHAR(100) NOT NULL
)
GO
|