|
|
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/Workaround:
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.
|