THE SQL Server Blog Spot on the Web

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

Kalen Delaney

Can You Trust Your Data? -- Untrustworthy Constraints

I’ve had several metadata posts recently, all about SQL Server 2005. But yesterday, a colleague asked me a metadata question about SQL Server 2000. He wanted to know how to tell what constraints had been created with the NOCHECK option. This option tells SQL Server not to verify the constraint for existing data when a constraint is added using ALTER TABLE. SQL Server will only verify the constraint for new data added after the constraint is added.  Only Foreign Key and Check constraints can be created with NOCHECK. Since Primary Key and Unique constraints build a unique index, we can’t tell SQL Server to only verify uniqueness for new data. And Default constraints don’t mean anything once the data is added.

It turns out there isn’t anyplace that tells you specifically that a constraint was added to the table with NOCHECK. But the status column in sysobjects has a bit which indicates if a constraint is untrustworthy.

Let me digress a bit. If there is a constraint on the table that says a column must be greater than 0, any query you write that asks for all rows where the column is less than 0 are really fast to execute. But only if SQL Server is absolutely sure that all the rows obey the constraint. If you added the constraint with NOCHECK and there was already data in the table, there might be data in the column with negative values. In addition, SQL Server allows you to DISABLE a constraint temporarily, and then re-enable it. So during the time a constraint was disabled, negative values could have been inserted. So if there is any chance that the data is not guaranteed to conform to the constraint’s requirement, SQL Server needs to know about it when optimizing queries referencing the column with the constraint. Any constraint which may have been violated is considered untrustworthy, and if there are any untrustworthy constraints, the optimizer can’t make any assumptions about the values in the column.

Sysobjects has a row for every constraint, as well as for every table, view, function, etc. For constraints, which really have no existence apart from the table they are part of, the sysobjects.parent_id column contains the object id of the table. In the sysobjects.status column, the 2048 bit indicates that at some point, the constraint was not valid, so there is a chance there is bad data.

Here’s an example. Create a table with 3 columns, and then add a constraint to each column, checking to see if the column is positive. Constraint c2 will be added with NOCHECK, so existing data will not be verified.

use tempdb

GO

create table test_bad_data (a int, b int, c int)

GO

alter table test_bad_data add constraint c1 check(a >0)

GO

alter table test_bad_data with nocheck
           add constraint c2 check(b >0)

GO

alter table test_bad_data add constraint c3 check(a >0)

GO

This query shows all the constraints from the table:

SELECT name, status

FROM sysobjects

WHERE parent_obj = object_id('test_bad_data')

 

This query shows that only constraint c2 is untrustworthy:

SELECT  name AS [Constraint Name],
       object_name(parent_obj) as [Table Name]

FROM sysobjects

WHERE status & 2048 = 2048

Now alter the table to disable constraint c3, and then re-enable it. Because it has at one point been disabled, it is now considered untrustworthy.

 

ALTER TABLE test_bad_data NOCHECK constraint c3

GO

ALTER TABLE test_bad_data check constraint c3

GO

SELECT name AS [Constraint Name],
       object_name(parent_obj) AS [Table Name]

FROM sysobjects

WHERE status & 2048 = 2048

 

SQL Server 2005 makes it easier to check for untrustworthy constraints, but not nearly so much fun. The sys.check_constraints view has a column called ‘is_not_trusted’, so if you created the above table on SQL Server 2005, you could run the following:

 

SELECT name, is_not_trusted,
       object_name(parent_object_id) AS [Table Name],

FROM sys.check_constraints

WHERE parent_object_id = object_id('test_bad_data')

 

In addition, although SQL Server 2005 lets you query the sysobjects compatability view, the status column is not populated, so the only way to query the metadata for possibly untrustworthy data is to use the new catalog view.

 

Finally, in both SQL Server 2000 and 2005, you can actually see what data violates a constraint using a DBCC command:

 

DBCC CHECKCONSTRAINTS (test_bad_data)

 

-- Kalen

 

Published Tuesday, October 10, 2006 6:28 AM by Kalen Delaney

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

 

Mark Stephenson said:

Alternatively, you can use the "CnstIsNotTrusted" property of the OBJECTPROPERTY metadata function to identify constraints that are not trusted.  

For example:
SELECT
     TABLE_NAME,
     CONSTRAINT_NAME
     FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
     WHERE CONSTRAINT_TYPE IN ( 'FOREIGN KEY', 'CHECK' )
         AND OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'CnstIsNotTrusted') = 1
        ORDER BY
              TABLE_NAME,
              CONSTRAINT_NAME
October 11, 2006 2:41 PM
 

Kalen Delaney said:

Thanks Mark.  Not nearly as much fun as bit arithmetic, but it's documented. :-)

--Kalen
October 11, 2006 3:38 PM
 

Rama krishna said:

thanq u sir.I got a nice information and iam very satisity with  notes

March 16, 2007 3:58 AM
 

mohaaron said:

Will someone explain to me how the where clause WHERE status & 2048 = 2048 works? I have never seen this type of where clause before and don't understand it. I can see that the status values it returns are 2050 and a lot of bigger numbers that always seem to start with 16########.

April 4, 2007 1:03 PM
 

Carlos said:

Is there a way to force SQL Server to begin trusting a particular constraint again? Can one simply update the BIT?

April 28, 2008 1:16 PM
 

Kalen Delaney said:

Hi mohaaron

Bit arithmetic is a general programming topic, not a SQL SERVER topic. If you've never learned how to do bit arithmetic, this isn't the place to learn, and you should just avoid the topic. However, here is a tutorial you might find useful if you really want to learn:  

http://www.cprogramming.com/tutorial/bitwise_operators.html

Good Luck,

Kalen

April 28, 2008 4:28 PM
 

Kalen Delaney said:

Hi Carlos

In SQL 2005 and later, no updating of system information is possible, and I would be very cautious of changing this at the bit level.

A better way would be to disable the constraint again, and then re-enable forcing SQL Server to check the values as the constraint is re-enabled. The syntax looks strange, but it works:

ALTER TABLE test_bad_data NOCHECK constraint c3

GO

ALTER TABLE test_bad_data with check check constraint c3

Notice that it has a double 'check', but the first is part of 'with check' that means to check the data as the ALTER is done, and the second CHECK just means to re-enable the constraint.

HTH,

Kalen

April 28, 2008 5:28 PM
 

Paul Nedwek said:

I have a constraint that is listed as untrustworthy.  I updated the data to make it all valid (they were all NULL to begin with) and then ran the ALTER TABLE to re-enable the constraint.  The FK constraint is still listed as untrustworthy.  Any ideas?

August 5, 2008 10:38 AM
 

MARYAM said:

TNX

April 30, 2010 5:20 AM
 

John Neville said:

Thanks for the in-depth info :-)

So if a column is "guarded" by a constraint marked as untrustworthy, and the Query Optimizer ignores that constraint when building an execution plan, could this have a negative impact on performance? (or less-than-optimal impact)

If so, is this visible in the query execution plan?  What should we be looking for?

Alternatively should querying sysobjects for untrustworthy constraints be part of the housekeeping?

Many thanks,

John

May 25, 2010 11:01 AM
 

Steven Devaney said:

I have tried Kalen's suggestion of disabling the constraint and checking and re-enabling to no avail.

None of the foreign key constraints have become trusted.

August 19, 2010 4:34 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

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