THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Non-trusted constraints

A discussion in the newsgroups before the holidays lead to trusted constraints and performance. This inspired me to blog about it, but I decided to have some vacation first :-). Instead of having one long article, I'll do a two-part. This one is about non-trusted constraints in general, and another one will follow about non-trusted constraints and performance.

As you might know, we can disable check and foreign key constraints. This can be done when we create the constraint, for instance:

USE tempdb
CREATE TABLE t1(c1 int)
INSERT INTO t1(c1VALUES(-1)
GO
ALTER TABLE t1 WITH NOCHECK ADD CONSTRAINT CK_t1_c1 CHECK(c1 0)

Above allow us to add the constraint even though we have rows that violates the constraint. The constraint isn't disabled, but we (can) have rows in the table that violates the constraint - the constraint isn't trusted. We can also disable an existing constraint:

USE tempdb
CREATE TABLE t2(c1 INT CONSTRAINT CK_t2_c1 CHECK(c1 0) )
INSERT INTO t2(c1VALUES(1)
GO
ALTER TABLE t2 NOCHECK CONSTRAINT CK_t2_c1
GO
INSERT INTO t2(c1VALUES(-1)

Again, we now have rows in the table that violates the constraint. For the first example, the constraint is enabled, but we didn't check for existing rows when we added the constraint. If we try to add a row which violates the constraint, we get an error message. For the second example, the constraint isn't even enabled. We can enable a disabled constraint:

ALTER TABLE t2 CHECK CONSTRAINT CK_t2_c1
GO
INSERT INTO t2(c1VALUES(-1)

The immediate above INSERT command will fail with an error message. The constraint in table t2 is now enabled. But the constraint for both table t1 and table t2 are non-trusted. For table t1, we added the constraint with existing data, and told SQL Server to not check existing data. SQL Server cannot trust this constraint. For table t2, we disabled the constraint, added data, then enabled the constraint. SQL Server cannot trust the constraint because we might have added data which violates the constraint while the constraint was disabled. There's an easy way to check whether you have non-tructed constraints. For instance, for check constraints:

SELECT OBJECT_NAME(parent_object_idAS table_namename
FROM sys.check_constraints
WHERE is_not_trusted 1

Now, can we turn a non-trusted constraint into a trusted constraint? Yes. But we first need to get rid of offending data:

DELETE FROM t1 WHERE c1 0
DELETE FROM t2 WHERE c1 0

And now we want to make sure the constraints are trusted:

ALTER TABLE t1 WITH CHECK CHECK CONSTRAINT CK_t1_c1
ALTER TABLE t2 WITH CHECK CHECK CONSTRAINT CK_t2_c1

There's no typo above. "WITH CHECK" is validate the data, and "CHECK CONSTRAINT" is to enable the constraint.

So, why would we want to bother with disabling and non-trusted constraints? The purpose of constraints is to make sure that we have consistent data. Why would we want to break this in the first place? Well, rarely. But here are a coule of possible scenarios where one could consider disabling constraints:

  • We run some batch operation once a week. With constraint enabled, this takes 5 hours. With constraints disabled, it takes 1 hour. We "know" that the batch is written in a way so that it doesn't violate any of our constraints.
  • We want to expand the domain of allowable values for a column. Today we allow values Y and N. We will also allow value U for the column. This is implemented as a check constraint. We remove the existing constraint and add the new one (which also allow for U). We know that no of the existing rows cannot violate the new constraint since we expand the domain of allowable values. Adding the new constraint with NOCHECK is much faster.

Above examples might seem a bit ... constructed. I haven't encountered much non-trusted constraints in reality, but it has happended. And my initial goal was to talk about non-trusted constraints and performance, and this will come in the following blog post.

Published Saturday, January 12, 2008 5:13 PM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Tibor Karaszi said:

(See my part 1 article about non-trusted constraints in general: http://sqlblog.com/blogs/tibor_karaszi/archive/2008/01/12/non-trusted-constraints.aspx

January 12, 2008 11:51 AM
 

Alejandro Mesa said:

Hi Tibor,

It is always refreshing and educating reading from your blog.

First I want to add that you can use "DBCC CHECKCONSTRAINTS" to check the integrity of constraints in a table, before making them tusted.

Second, Hugo bloged about same theme long time ago in this same blog portal, so I wonder if there will be something new about trusted / non-trusted constraints, but I guess I will have to wait for the second part.

Can you trust your constraints?

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

Cheers,

AMB

January 13, 2008 8:06 PM
 

TiborKaraszi said:

Hi Alejandro!

Thanks :-)

Ahh, yes, I intended to mention DBCC CHECKCONSTRAINTS, but it got lost in the writings.

I initially wanted to mention the smartness of removing a whole from a query (the join example), but while writing, I decided to mention a few words about non-trusted constraints. So that is what made it a two-part (and also me being too lazy to check for older such articles ;-) ).

January 14, 2008 1:20 PM
 

Sameer Alibhai (SharpDeveloper) said:

I have seen non-trusted constraints used in the real world when you have some existing data (maybe from a 3rd party system or from a customer) that has been imported into your database that breaks some rules, and you cannot clean it up at the moment.  However you still don't want your system to allow such data from the user interface.  In that case you will add the constraint as non-trusted until you are able to clean up the data (if ever).

January 11, 2010 10:03 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement