SQL Server Error Messages - Msg 547 - ALTER TABLE¶

Error Message¶

Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'Constraint Name'.
The conflict occurred in database 'Database Name', table 'Table Name', column 'Column Name'.

Causes:¶

This error occurs when creating a FOREIGN KEY constraint on a table and the values from the columns specified in the FOREIGN KEY constraint does not exist in the values of the columns designated as the PRIMARY KEY on the other table.

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

CREATE TABLE [dbo].[Currency] (
    [Code]    CHAR(3) NOT NULL PRIMARY KEY,
    [Name]    VARCHAR(50)
)

CREATE TABLE [dbo].[Currency Conversion] (
    [FromCurrencyCode]    CHAR(3),
    [ToCurrencyCode]      CHAR(3),
    [Conversion]          MONEY
)

And here’s some sample data from these tables:

[dbo].[Currency]
Code Name                                               
---- ----------------------
EUR  Euro
GBP  United Kingdom Pounds
USD  United States Dollar

[dbo].[Currency Conversion]
FromCurrencyCode ToCurrencyCode Conversion            
---------------- -------------- --------------------- 
GBP              USD            1.7508
EUR              USD            1.2107
USD              CAD            1.1482
USD              GBP            .5711
USD              EUR            .8261
USD              AUD            1.3680

You’ve decided to create a FOREIGN KEY relationship between the [FromCurrencyCode] and [ToCurrencyCode] columns of the [dbo].[Currency Conversion] table with the [dbo].[Currency].[Code] column.

ALTER TABLE [dbo].[Currency Conversion]
ADD CONSTRAINT FK_FromCurrencyCode_Currency_Code FOREIGN KEY ( [FromCurrencyCode] )
REFERENCES [dbo].[Currency] ( [Code] )

ALTER TABLE [dbo].[Currency Conversion]
ADD CONSTRAINT FK_ToCurrencyCode_Currency_Code FOREIGN KEY ( [ToCurrencyCode] )
REFERENCES [dbo].[Currency] ( [Code] )

No error is generated by the first FOREIGN KEY constraint on the FromCurrencyCode column since all the values in that column exist in the [dbo].[Currency] table. However the following error is generated by the second FOREIGN KEY constraint on the ToCurrencyCode column because there are values in that column that does not exist in the [dbo].[Currency] table, such as the CAD (Canadian Dollars) and AUD (Australian Dollars):

Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_ToCurrencyCode_Currency_Code'.
The conflict occurred in database 'SQLServerHelper', table 'Currency', column 'Code'.

Solution / Work Around:¶

When creating a FOREIGN KEY relationship between two tables, make sure that the column values from the second table exists in the column designated as the PRIMARY KEY on the primary table. To identify column values that do not exist in the primary table, you can do something like the following:

SELECT DISTINCT [FromCurrencyCode] 
FROM [dbo].[Currency Conversion] CC
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Currency] Curr
                  WHERE CC.[FromCurrencyCode] = Curr.[Code])

SELECT DISTINCT [ToCurrencyCode] 
FROM [dbo].[Currency Conversion] CC
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Currency] Curr
                  WHERE CC.[ToCurrencyCode] = Curr.[Code])

This will identify the currency codes that are not in the [dbo].[Currency] table. Once you’ve identified those missing column values, insert them in the primary table and then create the FOREIGN KEY constraint.