Problem
Adding constraints such as check constraints or foreign keys to a table are best practices to keep your data as clean as possible with minimal data enforcement rules performed at the database level. Unfortunately sometimes issues may occur where the data becomes out of synch and one of these constraints has been violated. This may be due to disabled constraints or constraints that are later added with the NOCHECK option. Finding these issues can be done by running queries to check each of the constraints, but is there any easier way to determine if the data the constraints support has been violated?
Solution
As mentioned already, one approach would be to write queries for each of the constraints and check the data to see if the constraints are being enforced. This is probably not all that difficult, but it could be time consuming. Another approach to tackle this issue is to use the DBCC CHECKCONSTRAINTS command. This command allows you to check the constraints to ensure that no data is violating the constraints that have been setup.
This command can be run as follows:
DBCC CHECKCONSTRAINTS (TableName) - checks an individual table
DBCC CHECKCONSTRAINTS (ConstraintName) - checks an individual constraint
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS - checks all constraints in the database
DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS - returns all rows that violate constraints
DBCC CHECKCONSTRAINTS WITH NO_INFOMSGS - suppress messages when query runs