|
|
Posted by Erland Sommarskog on 09/26/61 11:19
DW (dwhittier@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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|