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
Hi, Thanks so much, this page is excellent! Details, Clarity, Organise and Brilliant!
ReplyDeleteThe 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.
ReplyDeleteGazillion thanks. this fixed a massive issue for me. Patrick from South Africa...
ReplyDeleteAs 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.
Crafty stuff as always, really a true admirer of your work. Homestayalmora
ReplyDeleteHello, Many thanks; this article is really great like slope game!
ReplyDeleteThis tutorial on the SQL Server error "ALTER TABLE statement conflicted with the FOREIGN KEY constraint" in Part 69 provides clear guidance on resolving foreign key conflicts. It explains the issue in detail and offers practical steps to ensure smooth table alterations. Foer more visit Official Website.
ReplyDelete