Foreign keys and differences between CHECK, NOCHECK and WITH CHECK options

It’s confusing that developers are still doing fails when using CHECK, NOCHECK and WITH CHECK/NOCHECK clauses because they don’t know exactly what are differences between them. To be honest, it’s not a very native and clear part of SQL syntax. Let’s go to make this a little bit more clear and repeat some fundamental things.

First I will create two tables with data which I will later try to connect with the foreign key:

DROP TABLE [dbo].[SalesOrderItem]
DROP TABLE [dbo].[SalesOrder]

GO

CREATE TABLE dbo.SalesOrder
(
	SalesOrder_ID INT NOT NULL PRIMARY KEY
)
GO

CREATE TABLE dbo.SalesOrderItem
( SalesOrderItem_ID INT NOT NULL PRIMARY KEY,
 SalesOrder_ID INT NOT NULL
 )
 GO

 INSERT INTO dbo.[SalesOrder] 
	( [SalesOrder_ID] )
	VALUES (1), (2), (3), (4), (5)
GO

INSERT INTO dbo.[SalesOrderItem] ( [SalesOrderItem_ID], [SalesOrder_ID] )
	VALUES (1,1), (2,2), (3,3), (4,4), (5,5), (6,6)
GO

As you can see we did a mistake at row six: data are inconsistent because no such Order exists in dbo.SalesOrder table with SalesOrder_ID = 6. We will test it with different kinds of foreign key creation now.

We will try to create a foreign key between tables dbo.SalesOrderItem and dbo.SalesOrder using WITH CHECK constraint now:

ALTER TABLE [dbo].[SalesOrderItem] WITH CHECK ADD 
	CONSTRAINT [FK_SalesOrderItem_SalesOrder_SalesOrder_ID] 
		FOREIGN KEY ([SalesOrder_ID]) REFERENCES [dbo].[SalesOrder]([SalesOrder_ID])
GO

Key creation has failed because clause WITH CHECK means that SQL Server will check the consistency of existing data and create the foreign key only in case when all data match to constraint enforced by the key.

Yes, there is an option how to skip this consistency check and accept that the key will be created and used for future data, but existing data will stay inconsistent:

ALTER TABLE [dbo].[SalesOrderItem] WITH NOCHECK ADD 
	CONSTRAINT [FK_SalesOrderItem_SalesOrder_SalesOrder_ID] 
		FOREIGN KEY ([SalesOrder_ID]) REFERENCES [dbo].[SalesOrder]([SalesOrder_ID])
GO

Except data to be inconsistent is looks like there is no other issue using the NOCHECK option. But that’s not true. We have created a serious problem for the performance of future queries executed over these two tables. Foreign key created using NOCHECK option was created as an untrusted one which means that it won’t be used by query optimizer when building the most effective execution plan later. We can verify that fact in metadata:

SELECT name, [is_not_trusted] FROM sys.[foreign_keys]
GO

You can read more about the difference between trusted and untrusted foreign keys in this post.

We will try to delete a few rows from dbo.SalesOrder table now: first the row number 6 without valid reference to dbo.SalesOrder table and then the one with number 1  having matching Order:

DELETE FROM [dbo].[SalesOrder] WHERE [SalesOrder_ID] = 6
GO

DELETE FROM [dbo].[SalesOrder] WHERE [SalesOrder_ID] = 1
GO

First DELETE statement was successful and the second one failed because there exists real reference between the Order and OrderItem.

If we would like to override this error we can switch the foreign key temporary off using NOCHECK option:

ALTER TABLE dbo.[SalesOrderItem] NOCHECK CONSTRAINT [FK_SalesOrderItem_SalesOrder_SalesOrder_ID]
GO

DELETE FROM [dbo].[SalesOrder] WHERE [SalesOrder_ID] = 1
GO

With disabled foreign keys we can do any operations with data like when the key isn’t presented. But from that moment our data are uncontrolled and any kind of inconsistency is possible. And, as mentioned above, we caused the key to be untrusted with all the difficulties mentioned above.

Most mistakes are happening when we will try to adjust the key to be used again. There are two ways how to achieve that:

ALTER TABLE dbo.[SalesOrderItem] 
CHECK CONSTRAINT [FK_SalesOrderItem_SalesOrder_SalesOrder_ID]
GO

SQL Server will activate foreign key for future data changes but doesn’t check if existing data are consistent and the key is still marked as untrusted in the metadata. Therefor statement has been completed successfully.

Adding WITH CHECK option will modify this behavior to be strict and enforce data integrity check before foreign key creation:

ALTER TABLE dbo.[SalesOrderItem] 
WITH CHECK
CHECK CONSTRAINT [FK_SalesOrderItem_SalesOrder_SalesOrder_ID]
GO

The statement has failed because we have deleted Order with number 1 from table dbo.SalesOrder in the previous testing. We should first correct the data and delete the invalid row from dbo.SalesOrderItem or add Order with number 1 into dbo.SalesOrder table again.

Proper management of foreign keys is a key factor if we would like to have consistent and well-performing databases.