Razvan Socol wrote:
> Hello, ConfNoob
>
> There are two places where you can specify CHECK or NOCHECK in an ALTER
> TABLE statement:
>
> One place is where you specify WITH in front of CHECK/NOCHECK (and this
> means that the existing data should be checked or not); this is
> equivalent to the "Check Existing Data On Creation Or Re-Enabling"
> option in SSMS.
>
> The other place is where you don't specify WITH (and this means that
> the constraint will check the future inserts/updates in the table);
> this is equivalent to the "Enforce For INSERTs And UPDATEs" option is
> SSMS.
>
> For example, try this:
> CREATE TABLE T1 (X INT)
>
> INSERT INTO T1 VALUES (-2)
>
> ALTER TABLE T1 WITH NOCHECK ADD CONSTRAINT CK1 CHECK (X>0)
> --works fine, because we specified that we don't want to check the
> existing data
>
> EXEC sp_helpconstraint 't1'
> --it shows that the constraint is enabled, even if we didn't check the
> existing data when we created the constraint
>
> SELECT CASE OBJECTPROPERTY(OBJECT_ID('CK1'),'CnstIsNotTrusted') WHEN 0
> THEN 'Constraint is trusted' ELSE 'Constraint is not trusted' END
> --constraint is not trusted, because we specified that we don't want to
> check the existing data when we created the constraint
>
> INSERT INTO T1 VALUES (-1)
> --doesn't work, because the constraint is still enabled, even if we
> didn't check the existing data when we created the constraint
>
> ALTER TABLE T1 NOCHECK CONSTRAINT CK1
>
> EXEC sp_helpconstraint 't1'
> --now the constraint is disabled
>
> INSERT INTO T1 VALUES (-1)
> --after we disabled the constraint, this works too
>
> DROP TABLE T1
>
> GO
> CREATE TABLE T2 (Y INT)
>
> ALTER TABLE T2 ADD CONSTRAINT CK2 CHECK (Y>0)
>
> ALTER TABLE T2 NOCHECK CONSTRAINT CK2
>
> INSERT INTO T2 VALUES (-3)
> --works, because the constraint is disabled
>
> ALTER TABLE T2 CHECK CONSTRAINT CK2
> --works, because we did not specify to check the existing data
>
> SELECT * FROM T2
>
> ALTER TABLE T2 WITH CHECK CHECK CONSTRAINT CK2
> --fails, because the existing data is not correct
>
> GO
> DELETE T2
>
> ALTER TABLE T2 WITH CHECK CHECK CONSTRAINT CK2
> --notice the double "CHECK": once we specified "CHECK" and before we
> specified "WITH CHECK"
>
> ALTER TABLE T2 CHECK CONSTRAINT CK2
>
> SELECT CASE OBJECTPROPERTY(OBJECT_ID('CK2'),'CnstIsNotTrusted') WHEN 0
> THEN 'Constraint is trusted' ELSE 'Constraint is not trusted' END
> --now the constraint is trusted, because we checked the existing data
> when we enabled it
>
> DROP TABLE T2
>
> You can see if the constraint is currently enabled using
> sp_helpconstraint, but you cannot see if the existing data was checked
> or not at the time of the creation.
>
> You can find out if all current data was checked (at some time, sooner
> or later), by using the CnstIsNotTrusted object property, like shown
> above.
>
> When you use SSMS, you can also see only if the constraint is currently
> enabled (by looking at the "Enforce For INSERTs And UPDATEs" options),
> but you cannot see if the existing data was checked or not at the time
> of the creation. The "Check Existing Data On Creation Or Re-Enabling"
> option specifies what WILL happen if you modify the constraint now.
>
> Razvan