Skip to content

SQL Server Error Messages - Msg 547 - UPDATE

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

  • Error Message 547 - Delete Statement

    DELETE statement conflicted with COLUMN REFERENCE constraint Constraint Name. The conflict occurred in database Database Name, table Table Name, column Column Name.

  • Error Message 245

    Syntax error converting the varchar value to a column of data type int.