SQL Constraints CHECK, NOCHECK, WITH CHECK, WITH NOCHECK

posted Aug 15, 2017, 12:12 PM by Kevin Foley
From a 2015 email...

I was researching disabling constraints and came across the posts below, which lead me to an OH MY moment.

http://geekswithblogs.net/dturner/archive/2011/01/31/sql-constraints-check-and-nocheck.aspx

http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx

http://sqlwithmanoj.com/2012/03/14/nocheck-vs-with-nocheck-while-creating-disabled-foreign-key-fk-constraint/

https://msdn.microsoft.com/en-us/library/ms190273(v=sql.105).aspx

To summarize, constraint parameters "NOCHECK" and "WITH NOCHECK" are two different things, as are their brethren "CHECK" and "WITH CHECK"

"WITH CHECK" and "WITH NOCHECK" determine if the constraint will be enforced on existing rows at the time of constraint is created or modified.

"CHECK" and "NOCHECK" determine if the constraint is enabled or disabled.  Microsoft should have just named these parameters ENABLE and DISABLED

You can't create a disabled constraint with "NOCHECK", but it can be disabled after creation by using "NOCHECK"

So what is the problem?

When a constraint is modified or created using "WITH NOCHECK" the SQL optimizer ignores the constraint which can result in table scans.  From the MS library...

The query optimizer does not consider constraints that are defined WITH NOCHECK. 
Such constraints are ignored until they are re-enabled by using 
ALTER TABLE <table> WITH CHECK CHECK CONSTRAINT ALL.

Ouch !!

The queries below will list un-trusted constraints where "WITH NOCHECK" should probably be removed

SELECT *
from sys.check_constraints
WHERE is_not_trusted = 1

SELECT *
from sys.foreign_keys
WHERE is_not_trusted = 1

If the number of un-trusted foreign keys is extremely high then the performance impact has to be considered.

The command below will do an alter table on all tables and constraints to issue a WITH CHECK CHECK.  It will enable the constraint and force a check of all rows.

exec sp_MSforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'

This would be fine to run on an empty DB, but a little scary to run on a large DB.

It would be better to check the constraints first via executing the output of the below - again be aware that on large DBs this may take a while 

exec sp_MSforeachtable @command1="PRINT '?';  DBCC CHECKCONSTRAINTS ([?])
Comments