Home > SQL Server Error Messages > Msg 157 - An aggregate may not appear in the set list of an UPDATE statement.

SQL Server Error Messages - Msg 157 - An aggregate may not appear in the set list of an UPDATE statement.

Home > SQL Server Error Messages > Msg 547 - 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. SQL Server Error Messages - Msg 547 - 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.

SQL Server Error Messages - Msg 547

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', 'Anasco 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', 'Anasco Municipio', 'PR' )

SQL Server Error Messages - Msg 547 - ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'Constraint Name'. The conflict occurred in database 'Database Name', table 'Table Name', column 'Column Name'.
SQL Server Error Messages - Msg 547 - UPDATE statement conflicted with COLUMN REFERENCE constraint Foreign Key 'Constraint Name'. The conflict occurred in database 'Database Name', table 'Table Name', column 'Column Name'.
SQL Server Error Messages - Msg 547 - DELETE statement conflicted with COLUMN REFERENCE constraint 'Constraint Name'. The conflict occurred in database 'Database Name', table 'Table Name', column 'Column Name'.