The ALTER TABLE statement conflicted with the FOREIGN KEY constraint The conflict occurred in database , table , column

When we executed:

ALTER TABLE dbo.FactAbc WITH CHECK ADD CONSTRAINT [FK_FactAbc_DimA1] FOREIGN KEY([A1Key1]) REFERENCES [DimA1] ([A1Key1])

We received the error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint The conflict occurred in database , table , column

It is due to the data violating FOREIGN KEY constraint.

Use the following query to find out the rows with invalid data:

SELECT * FROM <foreign_key_table> WHERE <foreign_key_column> NOT IN (SELECT <primary_key_column> FROM <primary_key_table>);

Once we know the data problem, we can go ahead and fix it.

Other option is to use NOCHECK while creating constraint.

ALTER TABLE dbo.FactAbc WITH NOCHECK ADD CONSTRAINT [FK_FactAbc_DimA1] FOREIGN KEY([A1Key1]) REFERENCES [DimA1] ([A1Key1])

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s