QUESTION: Status column of FK Constraints in sysobjects - ????

[DW]

Greetings:

I have to do a one-off forceful change of some data in a database. I
need to disable some FK constraints, make the data change, and then
re-enable the constraints.

My process will be:

ALTER TABLE TABLE1 NOCHECK CONSTRAINT FK_TABLE1_TABLE 2
UPDATE TABLE1 SET COLUMN1=2 WHERE COLUMN2='Proble m row'
ALTER TABLE TABLE1 CHECK CONSTRAINT FK_TABLE1_TABLE 2

I wanted to get some evidence that the constraints were back up after I
did the 'check constraint' statements, so I looked in sysobjects.
There's a status column for the constraints, but I can't make much
sense of the numbers. Most of the FK constraints that I have not
changed are of status 0, but when I NOCHECK a constraint, it goes to
2304. When I CHECK it again, it's 2408.

Can someone explain this, and suggest a way to double-check that my
constraints are in fact enabled once I run the CHECK statement?

Thanks!!

[Erland Sommarskog]

DW (dw*******@shaw .ca) writes:

I have to do a one-off forceful change of some data in a database. I
need to disable some FK constraints, make the data change, and then
re-enable the constraints.

My process will be:

ALTER TABLE TABLE1 NOCHECK CONSTRAINT FK_TABLE1_TABLE 2
UPDATE TABLE1 SET COLUMN1=2 WHERE COLUMN2='Proble m row'
ALTER TABLE TABLE1 CHECK CONSTRAINT FK_TABLE1_TABLE 2

I wanted to get some evidence that the constraints were back up after I
did the 'check constraint' statements, so I looked in sysobjects.
There's a status column for the constraints, but I can't make much
sense of the numbers. Most of the FK constraints that I have not
changed are of status 0, but when I NOCHECK a constraint, it goes to
2304. When I CHECK it again, it's 2408.

This is because there are two status bits involved here. But rather
than looking at status bits, use the function Objectproperty( ) instead.
There are two property that applies to constraints: CnstIsDisabled
and CnstIsNotTruste d. (For the exaxt names, please look them up in
Books Online.)

If you like above, CnstIsDisabled will go back to, but CnstIsNotTruste d
will remain 1. This is because you when you re-enable a constraint,
WITH NOCHECK is the default. That is, this:

ALTER TABLE TABLE1 CHECK CONSTRAINT FK_TABLE1_TABLE 2

is equvivalent with:

ALTER TABLE TABLE1 WITH NOCHECK CHECK CONSTRAINT FK_TABLE1_TABLE 2

WITH NOCHECK means that the constraint is re-enabled without testing.
The advantages is that reapplying is fast - and if that data you sneaked
in violates the constraint, it is not trapped. But there is a downside.
"Not trusted", means that as far as the optimizer is concerned, the
constraint does not exist. This may have no impact on performance at all,
or it may be a disaster. One typical case of the latter is the
partitioning CHECK constraint for a partioned view.

Generally, I would advice of adding data that violates constraints in
a database. The optimizer knows that the constraint is not to be
trusted, but a programmer who looks at the data model, may assume that
the constraint is valid.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[DW]

Hmm. The data change won't be violated, but just to be sure, I used

ALTER TABLE TABLE1 WITH CHECK CHECK CONSTRAINT FK_TABLE1_TABLE 2

It took a little longer, which would be explained by it checking the
existing data.

But, after re-enabling the constraints, I ran
SELECT NAME, OBJECTPROPERTY (ID, 'CNSTISDISABLED ') FROM SYSOBJECTS
WHERE NAME LIKE 'FK%'

and
SELECT NAME, OBJECTPROPERTY (ID, 'CNSTISNOTTRUST ED') FROM SYSOBJECTS
WHERE NAME LIKE 'FK%'

And the results came back with all 0's.

Thanks for that! I likely would not have reenabled these things
properly otherwise.

DW