The ALTER TABLE statement conflicted with the FOREIGN KEY constraint in SQL Server - SQL Sever / TSQL Tutorial Part 69

Scenario:

You have created two tables dbo.Customer and dbo.Orders without having primary-foreign key relationship. After creating tables you inserted few records. Later you realized that you were supposed to add Foreign Key Constraint. When you tried to alter dbo.Orders table , you received error.

Create dbo.Customer and Dbo.Order Tables by using below script

USE YourDatabaseName
GO

CREATE TABLE dbo.Customer (
    Customerid INT PRIMARY KEY
    ,FName VARCHAR(100)
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10)
    )

CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT,
    CustomerId int
    )

Insert sample records by using below script.

     INSERT INTO dbo.Customer 
    (CustomerId,FName, LName,SSN)
     VALUES
    (1,'Aamir','Shahzad','000-000-00')

    INSERT INTO dbo.Orders
    (OrderItemName,OrderItemAmt,Customerid)
    values ('TV',2,2)

Now let's add Foreign Key Constraint

    Alter table dbo.Orders
    Add Constraint Fk_CustomerId  
    Foreign Key(CustomerId) References dbo.Customer(CustomerId)



When we execute above script, we get below error.

Msg 547, Level 16, State 0, Line 31
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "Fk_CustomerId". The conflict occurred in database "YourDatabaseName", table "dbo.Customer", column 'Customerid'.

As dbo.Customer has value 1 for CustomerId column and in dbo.Orders table column CustomerId has value 2. The values does not match with each other. That is the reason we received above error.

Solutions:

1) Fix the data in second table (dbo.Orders)
We can fix the data in second table and update the CustomerId column values. Once we will have correct data that matches with our Primary Table ( Dbo.Customer.CustomerId), it will let us create Foreign Key Constraint without any issue.

2) Use Alter Table with Nocheck ( Ignore existing data)
If you don't care about relationship of existing data. You can use With NoCheck with alter table statement and it will ignore the check to validate data and create Foreign Key Constraint. Once the Foreign Key Constraint will be created, it will enforce integrity for any new records inserted.

    Alter table dbo.Orders with Nocheck
    Add Constraint Fk_CustomerId  
    Foreign Key(CustomerId) References dbo.Customer(CustomerId)




Video Demo


4 comments:

  1. Hi, Thanks so much, this page is excellent! Details, Clarity, Organise and Brilliant!

    ReplyDelete
  2. The objective of geometry dash lite is to guide a small square character through a series of levels filled with obstacles, while collecting stars and avoiding death.

    ReplyDelete
  3. Gazillion thanks. this fixed a massive issue for me. Patrick from South Africa...

    As dbo.Customer has value 1 for CustomerId column and in dbo.Orders table column CustomerId has value 2. The values does not match with each other. That is the reason we received above error.

    ReplyDelete
  4. Crafty stuff as always, really a true admirer of your work. Homestayalmora

    ReplyDelete