SQL Server Error Messages - Msg 547 - INSERT¶

Error Message¶

Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN FOREIGN KEY 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 when performing an INSERT command on a table and one of the columns of the table references a primary key on another table and the value being inserted to that particular column does not exist in the other table.

To illustrate, let’s say you have the following tables:

CREATE TABLE [dbo].[State] (
    [StateCode]    CHAR(2) NOT NULL PRIMARY KEY,
    [StateName]    VARCHAR(50)
)

CREATE TABLE [dbo].[County] (
    [CountyCode]   CHAR(5) NOT NULL PRIMARY KEY,
    [CountyName]   VARCHAR(50),
    [StateCode]    CHAR(2) REFERENCES [dbo].[State] ( [StateCode] )
)

Your [dbo].[State] table contains the different states of the United States but does not yet include Puerto Rico. Since Puerto Rico is not yet included in your [dbo].[State] table, doing an insert into the [dbo].[County] table to add a county of Puerto Rico will generate the error:

INSERT INTO [dbo].[County] ( [CountyCode], [CountyName], [StateCode] )
VALUES ( '72011', 'Añasco Municipio', 'PR' )

Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK__County__StateCod__43D61337'.
The conflict occurred in database 'SQLServerHelper', table 'State', column 'StateCode'.
The statement has been terminated.

Solution / Work Around:¶

To avoid this error from happening, make sure that the value you are inserting into a column that references another table exists in that table. If the value does not exist in the primary table, insert to that table first before doing the insert on the second table.

To avoid the error in the example above, Puerto Rico needs to be inserted to the [dbo].[State] table first before the county can be inserted to the [dbo].[County] table:

INSERT INTO [dbo].[State] ( [StateCode], [StateName] )
VALUES ( 'PR', 'Puerto Rico' )

INSERT INTO [dbo].[County] ( [CountyCode], [CountyName], [StateCode] )
VALUES ( '72011', 'Añasco Municipio', 'PR' )