|
|
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.
|