Skip to content

The Dummy Programmer

Stories of daily programming

  • Home
  • The Dummy Programmer Robot
    • Overview
    • Version history & downloads
    • Tutorials
      • TDP Robot – The basics
      • A simple SQL Server backup solution
      • A simple SQL Server backup solution – A next step
  • My other dummy projects
  • Games
    • Space Y: An Asteroids clone
  • Services
    • What’s my user agent
    • What’s my IP address
  • About
  • Toggle search form

Sql Server add constraint WITH NOCHECK

Posted on March 21, 2020May 3, 2020 By

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!

Sql Server, Sql Server 2016

Post navigation

Previous Post: Get SQL Server version in T-SQL
Next Post: A chat program built in C# with .NET socket library

Related Posts

Oracle JRE 7 Update 51 (64-Bit) or higher is required for Polybase Sql Server 2016
How to know who is blocking a query in Sql Server Sql Server
Sql Server Instant File Initialization Sql Server
Getting SQL Server sample databases Sql Server
Reset Sql Server cache Sql Server
Sql Server error: A severe error occurred on the current command.  The results, if any, should be discarded. Sql Server

Search

Related posts

  • Get SQL Server version in T-SQL
  • Add a new website to an Apache installation
  • Reset Sql Server cache
  • How to know who is blocking a query in Sql Server

Categories

  • .NET 6 (1)
  • AJAX (1)
  • Android (2)
  • Apache (4)
  • ASP.NET (9)
  • ASP.NET MVC (3)
  • Avalonia UI (1)
  • BCP (1)
  • Bitlocker (2)
  • C# (14)
  • CentOS (4)
  • ClosedXML (1)
  • CLR (1)
  • DNS (1)
  • Encryption (3)
  • Excel (2)
  • FuelPHP (3)
  • Games (2)
  • Google Chrome (1)
  • GSuite (1)
  • HTML (1)
  • Imagick (2)
  • Javascript (1)
  • Kindle (1)
  • LAMP (3)
  • Linux (6)
  • MariaDB (2)
  • Mathematics (2)
  • MySql (4)
  • NPOI (1)
  • Office 365 (1)
  • Perl (1)
  • PHP (6)
  • Programming (1)
  • Remote desktop (1)
  • SFTP (2)
  • Sockets (3)
  • Sql Server (18)
  • Sql Server 2016 (14)
  • Sql Server 2019 (1)
  • SSL (2)
  • Task scheduler (1)
  • Telerik ASP.NET AJAX (2)
  • The Dummy Programmer Chat (2)
  • The Dummy Programmer Robot (6)
  • Threading (5)
  • Tools (1)
  • TPL (3)
  • TypeScript (3)
  • Ubuntu (4)
  • Virtualization software (3)
  • Visual Studio (1)
  • Visual Studio Code (2)
  • Web fonts (1)
  • Web programming (6)
  • Windows (12)
  • Windows 10 (15)
  • Windows Forms (1)
  • Windows Server (6)

Copyright © 2023 The Dummy Programmer | Privacy Policy | Terms of use |

Powered by PressBook Masonry Dark