By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,635 Members | 2,227 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,635 IT Pros & Developers. It's quick & easy.

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

P: n/a
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_TABLE2
UPDATE TABLE1 SET COLUMN1=2 WHERE COLUMN2='Problem row'
ALTER TABLE TABLE1 CHECK CONSTRAINT FK_TABLE1_TABLE2

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!!

Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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_TABLE2
UPDATE TABLE1 SET COLUMN1=2 WHERE COLUMN2='Problem row'
ALTER TABLE TABLE1 CHECK CONSTRAINT FK_TABLE1_TABLE2

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 CnstIsNotTrusted. (For the exaxt names, please look them up in
Books Online.)

If you like above, CnstIsDisabled will go back to, but CnstIsNotTrusted
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_TABLE2

is equvivalent with:

ALTER TABLE TABLE1 WITH NOCHECK CHECK CONSTRAINT FK_TABLE1_TABLE2

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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

P: n/a
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_TABLE2

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, 'CNSTISNOTTRUSTED') 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

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.