Presents your 
SQL SERVER E-NEWSLETTER for April 3, 2003

<------------------------------------------->

VALIDATE DATA WITH DBCC CHECKCONSTRAINTS

No matter how hard you try to keep your data clean, sometimes even the most
stringent constraints get violated. DBCC CHECKCONSTRAINTS can help you
find those pieces of rogue data by checking the integrity of foreign key
and checked constraints. The syntax is a follows:

DBCC CHECKCONSTRAINTS
     [('table_name'|'constraint_name'
)]
     [WITH {ALL_ERRORMSGS|ALL_CONSTRAINTS}]

You can check all enabled constraints on the entire table by using the
table name or a specific constraint using the constraint name.

The following example checks a specific table:

DBCC CHECKCONSTRAINTS ('authors')
Go

This example checks a specific constraint:

DBCC CHECKCONSTRAINTS  ('PartNo_Fkey')
GO

The ALL_CONSTRAINTS argument will check both enabled and disabled
constraints when using the table name. The ALL_ERRORMSGS argument 
returns rows that have violated the constraint.

DBCC CHECKCONSTRAINTS works by constructing and executing a query,
storing the data in a temp table and then returning a result set when 
all the requested tables have been checked.

The result set will include the table name, the constraint name, and 
the violation that has occurred.

For example, running DBCC CHECKCONSTRAINT on the orders table could
produce the following result:

Table Name        Constraint Name             Where
----------       -----------------     ---------------
orders             PartNo_Fkey         PartNo = '12'

PartNo='12' can now be used in a SELECT statement to find the row that
has violated the constraint. Then you can decide to delete the row, change
the part number to an acceptable number, or change the constraint to
accommodate the problem number.
----------------------------------------