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!:
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:
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!
