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

Оптимизация производительности Express.js бэкенда

Reangularity 23.05.2025

Express. js заслуженно остаётся одним из самых популярных инструментов для создания бэкенда, но даже он не застрахован от проблем с производительностью. Многие разработчики сталкиваются с ситуацией,. . .

Продвинутая обработка данных с LINQ в C#

stackOverflow 23.05.2025

LINQ (Language Integrated Query) — это фундаментальное изменение парадигмы работы с данными в C#. Простые запросы Where и Select знакомы любому разработчику, но настоящая мощь LINQ раскрывается в. . .

Инфраструктура PKI и сертификатов безопасности

Mr. Docker 23.05.2025

PKI (Public Key Infrastructure) — это невидимый фундамент цифрового доверия, без которого современный интернет просто рассыпался бы как карточный домик. За этой аббревиатурой скрывается целый. . .

Аутентификация OAuth в Python

py-thonny 22.05.2025

OAuth (Open Authorization) — это целый стандарт для делегированного доступа. Звучит занудно? Давайте проще: OAuth позволяет приложениям получать доступ к информации пользователя на сторонних сервисах. . .

Хеширование и соль паролей в веб-приложениях C#

stackOverflow 22.05.2025

Когда-то в начале своей карьеры я тоже грешил простейшими подходами к хранению паролей – MD5-хеширование казалось верхом защиты. Но технологии не стоят на месте, вычислительные мощьности растут, и. . .

Генераторы Python для эффективной обработки данных

AI_Generated 21.05.2025

В Python существует инструмент настолько мощный и в то же время недооценённый, что я часто сравниваю его с тайным оружием в арсенале программиста. Речь идёт о генераторах — одной из самых элегантных. . .

Чем заменить Swagger в .NET WebAPI

stackOverflow 21.05.2025

Если вы создавали Web API на . NET в последние несколько лет, то наверняка сталкивались с зелёным интерфейсом Swagger UI. Этот инструмент стал практически стандартом для документирования и. . .

Использование Linq2Db в проектах C# .NET

UnmanagedCoder 21.05.2025

Среди множества претендентов на корону «идеального ORM» особое место занимает Linq2Db — микро-ORM, балансирующий между мощью полноценных инструментов и легковесностью ручного написания SQL.

Что. . .

Реализация Domain-Driven Design с Java

Javaican 20.05.2025

DDD — это настоящий спасательный круг для проектов со сложной бизнес-логикой. Подход, предложенный Эриком Эвансом, позволяет создавать элегантные решения, которые точно отражают реальную предметную. . .

Возможности и нововведения C# 14

stackOverflow 20.05.2025

Выход версии C# 14, который ожидается вместе с . NET 10, приносит ряд интересных нововведений, действительно упрощающих жизнь разработчиков. Вы уже хотите опробовать эти новшества? Не проблема! Просто. . .

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!

How to Fix the «The ALTER TABLE statement conflicted with the FOREIGN KEY constraint» Error in SQL

So you’re trying to add a foreign key to the tblDomare table, but it’s throwing this error at you: «The ALTER TABLE statement conflicted with the FOREIGN KEY constraint ‘FK__tblDomare__PersNR__5F7E2DAC’. The conflict occurred in database ‘almu0004’, table ‘dbo.tblBana’, column ‘BanNR’.»

🤔 What does this error mean and how can you fix it? Don’t worry, we’ve got you covered! In this guide, we’ll break down the issue, provide easy solutions, and even give you a cool call-to-action at the end.

Understanding the Error

Let’s start by understanding what this error message is trying to tell us. Essentially, it’s saying that the FOREIGN KEY constraint you’re trying to add to the tblDomare table is conflicting with existing data in the tblBana table.

In simple terms, the foreign key you’re trying to add references a value in tblBana that doesn’t exist. This is because the value you’re referencing in tblBana‘s BanNR column is not present.

Finding the Culprit

To identify which specific data is causing the conflict, take a closer look at the values you inserted into the tblDomare and tblBana tables.

In this example, the tblBana table has three values in the BanNR column: 1, 2, and 3. However, the values you inserted in the tblDomare table’s PersNR column (6811034679, 7606091347, and 8508284163) do not match any of the values in tblBana.

Fixing the Error

Now that you understand the problem, it’s time to fix it. Here are a couple of solutions:

Solution 1: Add the missing values to tblBana

If the values in tblDomare‘s PersNR column are valid references to tblBana‘s BanNR column, you should add those missing values to the tblBana table. In this case, you would need to insert the values 6811034679, 7606091347, and 8508284163 into tblBana‘s BanNR column.

INSERT INTO tblBana (BanNR)
VALUES (6811034679);

INSERT INTO tblBana (BanNR)
VALUES (7606091347);

INSERT INTO tblBana (BanNR)
VALUES (8508284163);

After inserting the missing values, you can then proceed to add the foreign key without encountering any conflicts.

Solution 2: Update the values in tblDomare

If the values in tblDomare‘s PersNR column are not intended to be foreign keys referencing tblBana‘s BanNR column, you should update those values to valid references or remove the foreign key constraint altogether.

For example, if the PersNR column should match the BanNR column in tblBana, you would update the tblDomare table as follows:

UPDATE tblDomare
SET PersNR = '1'
WHERE PersNR = '6811034679';

UPDATE tblDomare
SET PersNR = '2'
WHERE PersNR = '7606091347';

UPDATE tblDomare
SET PersNR = '3'
WHERE PersNR = '8508284163';

After updating the values in tblDomare, you can then add the foreign key constraint without any conflicts.

Conclusion

Congratulations! You’ve now fixed the «The ALTER TABLE statement conflicted with the FOREIGN KEY constraint» error in SQL. Whether you added the missing values to tblBana or updated the values in tblDomare, you’re no longer encountering conflicts when adding the foreign key.

If you found this guide helpful, make sure to share it with your fellow SQL enthusiasts. And if you have any other SQL queries or tech questions, feel free to reach out and engage with us. We’re always here to help! 😊🚀