How to Find All Constraint Violations in a SQL Server Database

You can run the DBCC CHECKCONSTRAINTS console command to return a list of all constraint violations in a SQL Server database.

This command checks the integrity of a specified constraint or all constraints on a specified table in the current database. It returns any foreign key and CHECK constraint violations that it finds.

You can use the ALL_CONSTRAINTS option to check both enabled and disabled constraints. If you omit this, then only enabled constraints are returned (unless you explicitly specify a constraint to check, in which case it will be returned regardless of whether it’s enabled or disabled).

Example 1 – Violated CHECK Constraints

I ran this example against a database that contains some CHECK constraint violations.

USE Test;
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS;

Result:

+------------------------+-------------------+---------------------------------------------------------+
| Table                  | Constraint        | Where                                                   |
|------------------------+-------------------+---------------------------------------------------------|
| [dbo].[Occupation]     | [chkJobTitle]     | [JobTitle] = 'Digital Nomad'                            |
| [dbo].[ConstraintTest] | [chkValidEndDate] | [StartDate] = '2020-01-01' AND [EndDate] = '1999-01-01' |
| [dbo].[ConstraintTest] | [chkValidEndDate] | [StartDate] = '2021-10-25' AND [EndDate] = '2021-10-24' |
+------------------------+-------------------+---------------------------------------------------------+

This shows that I have three constraint violations in my database.

Explanation of the Columns

The three columns return the following information:

Table: Name of the table name that contains the constraint violation.
Constraint: Name of the constraint that is violated.
Where: Column value assignments that identify the row or rows violating the constraint. The value in this column can be used in a WHERE clause of a SELECT statement querying for rows that violate the constraint.

Therefore, thanks to the third column, I can now find (and update) all invalid data.

Find the Invalid Data

So if we look at the first row from my DBCC CHECKCONSTRAINTS results, we see that we can find the offending data by using [JobTitle] = 'Digital Nomad' in a WHERE clause.

Like this:

SELECT *
FROM [dbo].[Occupation]
WHERE [JobTitle] = 'Digital Nomad';

Result:

+----------------+---------------+
| OccupationId   | JobTitle      |
|----------------+---------------|
| 7              | Digital Nomad |
+----------------+---------------+

The Constraint Definition

Let’s take a look at the actual definition for the chkJobTitle constraint:

SELECT Definition 
FROM sys.check_constraints
WHERE name = 'chkJobTitle';

Result:

+-------------------------------+
| Definition                    |
|-------------------------------|
| ([JobTitle]<>'Digital Nomad') |
+-------------------------------+

This constraint says that the value of the JobTitle column must not be Digital Nomad, yet a digital nomad still managed to get into my database!

Update the Offending Data

You can either update the offending data, delete it, or leave it alone.

In this example I use the same WHERE clause to update the value:

UPDATE [dbo].[Occupation]
SET [JobTitle] = 'Unemployed'
WHERE [JobTitle] = 'Digital Nomad';

Now if I run the check again, that record is no longer an issue, and only the other two issues remain:

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS;

Result:

+------------------------+-------------------+---------------------------------------------------------+
| Table                  | Constraint        | Where                                                   |
|------------------------+-------------------+---------------------------------------------------------|
| [dbo].[ConstraintTest] | [chkValidEndDate] | [StartDate] = '2020-01-01' AND [EndDate] = '1999-01-01' |
| [dbo].[ConstraintTest] | [chkValidEndDate] | [StartDate] = '2021-10-25' AND [EndDate] = '2021-10-24' |
+------------------------+-------------------+---------------------------------------------------------+

Example 2 – Violated Foreign Key Constraints

In this example I switch to a database that contains a couple of foreign key constraint violations.

USE Music;
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS;

Result:

+----------------+---------------------+--------------------+
| Table          | Constraint          | Where              |
|----------------+---------------------+--------------------|
| [dbo].[Albums] | [FK_Albums_Artists] | [ArtistId] = '123' |
| [dbo].[Albums] | [FK_Albums_Artists] | [ArtistId] = '17'  |
+----------------+---------------------+--------------------+

In this case, it appears that two rows in the Albums table are referencing an ArtistId that doesn’t exist.

Find the Invalid Data

Again, we can use the Where column to construct our WHERE clause. This time I’ll add both violations to my WHERE clause:

SELECT *
FROM [dbo].[Albums]
WHERE [ArtistId] = '123' OR [ArtistId] = '17';

Result:

+-----------+-------------+---------------+------------+-----------+
| AlbumId   | AlbumName   | ReleaseDate   | ArtistId   | GenreId   |
|-----------+-------------+---------------+------------+-----------|
| 21        | Yo Wassup   | 2019-03-12    | 17         | 3         |
| 22        | Busted      | 1901-05-11    | 123        | 3         |
+-----------+-------------+---------------+------------+-----------+

So we can now see the two rows that violate the constraint (although it’s only the ArtistId column that violates the constraint).

Check the Primary Key Table

We can confirm the violation by querying the Artists table (i.e. the table that contains the primary key for this foreign key).

So let’s run the same query against the Artists table.

SELECT *
FROM [dbo].[Artists]
WHERE [ArtistId] = '123' OR [ArtistId] = '17';

Result:

(0 rows affected)

As expected, neither value is in that table.

The foreign key is supposed to prevent this from happening. Either the invalid data entered the database while the foreign key was disabled, or it entered before the foreign key was created. Either way, when creating or enabling a foreign key or CHECK constraint, you should use WITH CHECK to specify that all existing data should be checked before enabling the constraint.

Example 3 – Check Only Enabled Constraints

If you only want to check constraints that are currently enabled, remove WITH ALL_CONSTRAINTS:

USE Test;
DBCC CHECKCONSTRAINTS;

Result:

+--------------------+---------------+------------------------------+
| Table              | Constraint    | Where                        |
|--------------------+---------------+------------------------------|
| [dbo].[Occupation] | [chkJobTitle] | [JobTitle] = 'Digital Nomad' |
+--------------------+---------------+------------------------------+

So out of the two constraints that were violated, it appears that chkJobTitle is the only one that was enabled.

We can further verify this with the following query:

SELECT 
  name,
  is_disabled
FROM sys.check_constraints
WHERE name = 'chkValidEndDate' OR name = 'chkJobTitle';

Result:

+-----------------+---------------+
| name            | is_disabled   |
|-----------------+---------------|
| chkJobTitle     | 0             |
| chkValidEndDate | 1             |
+-----------------+---------------+

Example 4 – Check Only Constraints for a Given Table

You can add the name of a table in parentheses if you only want to check the constraints for that table:

USE Test;
DBCC CHECKCONSTRAINTS(ConstraintTest) WITH ALL_CONSTRAINTS;

Result:

+------------------------+-------------------+---------------------------------------------------------+
| Table                  | Constraint        | Where                                                   |
|------------------------+-------------------+---------------------------------------------------------|
| [dbo].[ConstraintTest] | [chkValidEndDate] | [StartDate] = '2020-01-01' AND [EndDate] = '1999-01-01' |
| [dbo].[ConstraintTest] | [chkValidEndDate] | [StartDate] = '2021-10-25' AND [EndDate] = '2021-10-24' |
+------------------------+-------------------+---------------------------------------------------------+

Example 5 – Check a Single Constraint

You can check a single constraint by enclosing its name inside parantheses:

USE Test;
DBCC CHECKCONSTRAINTS(chkValidEndDate);

Result:

+------------------------+-------------------+---------------------------------------------------------+
| Table                  | Constraint        | Where                                                   |
|------------------------+-------------------+---------------------------------------------------------|
| [dbo].[ConstraintTest] | [chkValidEndDate] | [StartDate] = '2020-01-01' AND [EndDate] = '1999-01-01' |
| [dbo].[ConstraintTest] | [chkValidEndDate] | [StartDate] = '2021-10-25' AND [EndDate] = '2021-10-24' |
+------------------------+-------------------+---------------------------------------------------------+

When you specify a single constraint, the WITH ALL_CONSTRAINTS has no effect:

USE Test;
DBCC CHECKCONSTRAINTS(chkValidEndDate) WITH ALL_CONSTRAINTS;

Result:

+------------------------+-------------------+---------------------------------------------------------+
| Table                  | Constraint        | Where                                                   |
|------------------------+-------------------+---------------------------------------------------------|
| [dbo].[ConstraintTest] | [chkValidEndDate] | [StartDate] = '2020-01-01' AND [EndDate] = '1999-01-01' |
| [dbo].[ConstraintTest] | [chkValidEndDate] | [StartDate] = '2021-10-25' AND [EndDate] = '2021-10-24' |
+------------------------+-------------------+---------------------------------------------------------+