|
|
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/Workaround:
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' )
|