Sql Server add constraint WITH NOCHECK

Good morning guys!

Today I want to talk about a Sql Server feature that you could use when creating foreign key constraints: the WITH NOCHECK option.

If you look at the name it seems that with that option, the constraint is not enforced… but it is not true.

You can check this by running this script:

CREATE TABLE [dbo].[Child](
[ID] [int] NOT NULL PRIMARY KEY CLUSTERED,
[ParentID] [int] NOT NULL,
[ChildName] [varchar](10) NOT NULL,
)
GO

CREATE TABLE [dbo].[Parent](
[ID] [int] NOT NULL PRIMARY KEY CLUSTERED,
[ParentName] [varchar](10) NOT NULL 
) 
GO

ALTER TABLE [dbo].[Child] WITH NOCHECK ADD CONSTRAINT [FK_Child_Parent] FOREIGN KEY([ParentID])
REFERENCES [dbo].[Parent] ([ID])
GO

INSERT INTO [dbo].[Parent]([ID], [ParentName]) VALUES(1, 'Parent1')
INSERT INTO [dbo].[Child]([ID], [ParentID], [ChildName]) VALUES(1, 1, 'Child1')
INSERT INTO [dbo].[Child]([ID], [ParentID], [ChildName]) VALUES(2, 2, 'Child1')
GO

The second insert statement in the table Child will raise an error because the parent with ID 2 doesn’t exist.

Msg 547, Level 16, State 0, Line 26
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Child_Parent". The conflict occurred in database "TestDatabase", table "dbo.Parent", column 'ID'.
The statement has been terminated.

To see the difference, first of all try to execute this slightly modified version of the script above, in which we will create the table and fill it with data:

-- First of all, drop the tables!!
DROP TABLE [dbo].[Child]
GO

DROP TABLE [dbo].[Parent]
GO

CREATE TABLE [dbo].[Child](
	[ID] [int] NOT NULL PRIMARY KEY CLUSTERED,
	[ParentID] [int] NOT NULL,
	[ChildName] [varchar](10) NOT NULL,
	)
GO

CREATE TABLE [dbo].[Parent](
	[ID] [int] NOT NULL PRIMARY KEY CLUSTERED,
	[ParentName] [varchar](10) NOT NULL 
) 
GO

INSERT INTO [dbo].[Parent]([ID], [ParentName]) VALUES(1, 'Parent1')
INSERT INTO [dbo].[Child]([ID], [ParentID], [ChildName]) VALUES(1, 1, 'Child1')
INSERT INTO [dbo].[Child]([ID], [ParentID], [ChildName]) VALUES(2, 2, 'Child1')
GO

Now we have a child record with no valid “ParentID” value, and if you try to create the constraint using the WITH NOCHECK option, the constraint will be created without errors!

ALTER TABLE [dbo].[Child] WITH NOCHECK ADD CONSTRAINT [FK_Child_Parent] FOREIGN KEY([ParentID])
REFERENCES [dbo].[Parent] ([ID])
GO

As demonstrated in the beginning of the post, in this situation you still can’t insert invalid “ParentID” values.

So the difference between “WITH NOCHECK” and “WITH CHECK” is that in the first case, no check is made on existing data while creating the constraint, so is possible that dirty data can exist in the table even after the creation of the constraint.

Apart from the presence of dirty data, which in itself is already a serious matter, be careful that a foreign key constraint created using the “WITH NOCHECK” is marked as “not trusted”, which means that Sql Server cannot use it in executions plan because of the possible presence of inconsistent data.

The following query extract the list of all the foreign keys of the database with a column indicating if the foreign key is trusted or not:

SELECT OBJECT_NAME(constid), OBJECTPROPERTY(constid, 'CnstIsNotTrusted') AS NotTrusted
FROM sys.sysforeignkeys

Bye bye friends!

Related Posts