Конфликт инструкции alter table с ограничением foreign key

JWT аутентификация в Java

Javaican 21.04.2025

JWT (JSON Web Token) представляет собой открытый стандарт (RFC 7519), который определяет компактный и самодостаточный способ передачи информации между сторонами в виде JSON-объекта. Эта информация. . .

Спринты Agile: Планирование, выполнение, ревью и ретроспектива

EggHead 21.04.2025

Спринты — сердцевина Agile-методологии, позволяющая командам создавать работающий продукт итерационно, с постоянной проверкой гипотез и адаптацией к изменениям. В основе концепции спринтов лежит. . .

Очередные открытия мега простых чисел, сделанные добровольцами с помощью домашних компьютеров

Programma_Boinc 21.04.2025

Очередные открытия мега простых чисел, сделанные добровольцами с помощью домашних компьютеров.

3 марта 2025 года, в результате обобщенного поиска простых чисел Ферма в PrimeGrid был найден. . .

Система статов в Unity

GameUnited 20.04.2025

Статы — фундаментальный элемент игрового дизайна, который определяет характеристики персонажей, предметов и других объектов в игровом мире. Будь то показатель силы в RPG, скорость передвижения в. . .

Статические свойства и методы в TypeScript

run.dev 20.04.2025

TypeScript прочно занял своё место в системе современной веб-разработки. Этот строго типизированный язык программирования не просто расширяет возможности JavaScript — он делает разработку более. . .

Batch Transform и Batch Gizmo Drawing API в Unity

GameUnited 20.04.2025

В мире разработки игр и приложений на Unity производительность всегда была критическим фактором успеха. Создатели игр постоянно балансируют между визуальной привлекательностью и плавностью работы. . .

Звук в Unity: Рандомизация с Audio Random Container

GameUnited 20.04.2025

В современных играх звуковое оформление часто становится элементом, который либо полностью погружает игрока в виртуальный мир, либо разрушает атмосферу за считанные минуты. Представьте: вы исследуете. . .

Максимальная производительность C#: Советы, тестирование и заключение

stackOverflow 20.04.2025

Погружение в мир микрооптимизаций C# открывает перед разработчиком целый арсенал мощных техник. Но как определить, где и когда их применять? Ответ начинается с точных измерений и профилирования.

. . .

Максимальная производительность C#: Предсказание ветвлений

stackOverflow 20.04.2025

Третий ключевой аспект низкоуровневой оптимизации — предсказание ветвлений. Эта тема менее известна среди разработчиков, но её влияние на производительность может быть колоссальным. Чтобы понять. . .

Максимальная производительность C#: Векторизация (SIMD)

stackOverflow 20.04.2025

Помимо работы с кэшем, другим ключевым аспектом низкоуровневой оптимизации является векторизация вычислений. SIMD (Single Instruction, Multiple Data) позволяет обрабатывать несколько элементов данных. . .

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

Reading Time: 4 minutes

When attempting to create a foreign key constraint on a table, you may receive an error message very similar to the following:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “constraint_name”. The conflict occurred in database “DatabaseName”, table “TableName”, column ‘ColumnName’.

This may be happening because you are attempting to create a foreign key constraint on a table that presently has data in it that violates the principle of the foreign key constraint.

When you establish a foreign key constraint in a child table, the child values must first exist in the parent table. Before we can reference a value in a child table, that value must first exist in the parent table.

Let’s work through a quick example. Let’s create two tables called Books and BookSales:

CREATE TABLE Books
(
   BookID INT PRIMARY KEY IDENTITY,
   Title VARCHAR(30),
   Author VARCHAR(20)
)

CREATE TABLE BookSales
(
   SaleID INT IDENTITY(1000,1),
   BookID INT,
   OrderDate DATETIME
)

Notice each table uses the IDENTITY property. This is very helpful for automatically generating a new number during an INSERT statement.

Now let’s add some rows to each table:

INSERT INTO Books (Title, Author)
VALUES
('As a man thinketh','Allen'),
('Deep Work','Newport')

INSERT INTO BookSales (BookID, OrderDate)
VALUES
(1, '7/1/2023'),
(1, '7/6/2023'),
(2, '7/7/2023'),
(99, '7/8/2023')

Here is what the content ought to look like:

Notice the BookID column of the BookSales table. This column is meant to contain references to the Books table so that we know which book was purchased in the sale.

Also notice Sale #1003. It references a book with an ID of 99, which doesn’t exist in the parent Books table.

That’s strange….

We did not create a foreign key constraint that would enforce a proper relationship between these two tables. We could have created a foreign key constraint on the BookID column of the BookSales table to ensure that an ID value referenced there needs to first exist in the parent Books table. That way, we know any BookID in the BookSales table references an existing row in the parent Books table!

Well, no biggie. Let’s attempt to go ahead and create that constraint:

ALTER TABLE BookSales ADD CONSTRAINT fk_BookID FOREIGN KEY (BookID) REFERENCES Books(BookID)

When we run this code, we get the following error message:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “fk_BookID”. The conflict occurred in database “SimpleSQLTutorials”, table “dbo.Books”, column ‘BookID’.

The reason we’re receiving this error message is because of Sale #1003. It references an ID that doesn’t exist in the parent table, which violates the whole principle of a foreign key constraint!

Two possible solutions

There are two ways to make this work. First, we can modify the data in the child table to reference an actual valid value that exists in the parent table. Then re-run our ADD CONSTRAINT statement to create the constraint.

In the real world, that might take a while. But if the data in the child table must have valid, proper references, that is what needs to be done.

But on the other hand, if you really don’t care about proper references, you can create the constraint and ask it to basically ignore values presently in the table and create the constraint anyway.

All we need to do in that case is add the WITH NOCHECK phrase to our statement:

ALTER TABLE BookSales WITH NOCHECK ADD CONSTRAINT fk_BookID FOREIGN KEY (BookID) REFERENCES Books(BookID)

If we run that statement, we see the constraint gets added just fine!:

Картинка с сайта: simplesqltutorials.com

We can see the constraint exists in our object explorer:

Going forward, invalid references cannot exist in the child table

I need to point out that when we add the WITH NOCHECK phrase, it does not alter the table forever to allow invalid references. It only applies when creating the constraint.

For example, now that this constraint exists, we cannot insert another row into the table that violates the principle of the foreign key constraint:

Картинка с сайта: simplesqltutorials.com

Next Steps:

Leave a comment if you found this tutorial helpful!

Everything discussed in this tutorial can also be found in the following FREE Ebook:

FREE Ebook on SQL Server Constraints!

This FREE Ebook contains absolutely everything you need to know about all the different constraints available to us in Microsoft SQL Server, including:

• Primary Key constraints
• Foreign Key constraints
• Default constraints
• Check constraints
• Unique constraints

Everything found in this tutorial is thoroughly discussed in the Ebook. This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Get it today!

This tutorial assumes you are already familiar with foreign key constraints. If you aren’t, take a look at the full beginner-friendly tutorial to learn more about foreign constraints and how they work:

SQL Server Foreign Key: Everything you need to know

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!