DBCC CheckConstraints

Being day three of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC CHECKCONSTRAINTS.

Description:

DBCC CHECKCONSTRAINTS is used to check the integrity of one constraint, all constraints for a table, or all constraints for a database.

DBCC CHECKCONSTRAINTS Syntax:

dbcc checkconstraints
[
    ( { 'table_name' | table_id | 'constraint_name' | constraint_id } )
]
    [ WITH
        { ALL_CONSTRAINTS | ALL_ERRORMSGS }
        [ , [ NO_INFOMSGS ] ]
    ]

Example:

To check constraints for an a single table:

DBCC CHECKCONSTRAINTS ('tablename');

For instance to check the constraints on the Production.Product table in the Adventureworks database you would run the following T-SQL.

DBCC CHECKCONSTRAINTS('Production.Product');

To check a single constraint on a table you would run the following:

DBCC CHECKCONSTRAINTS('constraint name');

To check constraints for an entire database use:

DBCC CHECKCONSTRAINTS;

Finding problems

If all of your constraints are enforced and trusted, you really shouldn’t run into any problems that would be detected by DBCC CHECKCONSTRAINTS. The problem arises when someone modifies a constraing or foreign key to use the WITH NOCHECK option, which effectively means create the constraint but don’t use it. You can also run into problems if someone disables a constraint with the intent of re-enabling it later and they forget to do that.

To simulate a problem that would be detected, I updated the [FK_Address_StateProvince_StateProvinceID] constraint in on the [Person].[Address] table in the AdventureWorks database. I then ran an update statement to change one of the StateProvinceID’s to be invalid, then checked the contents of the [Person].[Address] table as shown here:

UPDATE [Person].[Address]
 SET StateProvinceID = 3939393
 WHERE AddressID = 1;

SELECT [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [Person].[Address];

Next when DBCC CHECKCONSTRAINTS is run, it produces results showing exactly where the problem is.

Here we can see that the [Person].[Address] table is the problem where [StateProvinceID] = ‘3939393’. The next step would be to go and update that row to the right value, then add the WITH CHECK option to the constraint, and the constraint will be perfectly valid again.

Notes:

For more information see TSQL Wiki DBCC checkconstraints.

DBCC Command month at SteveStedman.com is almost as much fun as violating the integrity of a constraint.