Finding and fixing SQL Server database constraint issues

By: Greg Robidoux   |   Updated: 2023-05-19   |   Comments (7)   |   Related: > Constraints

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

Here are some examples of how you can use this command.

Example 1

Here is an example using the AdventureWorks2019 database where a foreign key constraint has been disabled to allow you to possibly do some bulk loading of data without checking the FK constraint for each record. This example disables the FK constraint, inserts new records and then tries to enable the FK constraint.

When the FK is enabled it will fail, because the new row violates the FK constraint.

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

We can disable the FK and update the record as shown below.

-- disable FK constraint
ALTER TABLE [Person].[Address]
NOCHECK CONSTRAINT FK_Address_StateProvince_StateProvinceID;

-- update row
UPDATE [Person].[Address]
SET [StateProvinceID] = 200
WHERE AddressID = 1

If we try to enable the FK as follows:

-- try to enable constraint
ALTER TABLE [Person].[Address]
WITH CHECK CHECK CONSTRAINT FK_Address_StateProvince_StateProvinceID;

We get this error message.

To find and fix this error we can run the DBCC CHECKCONSTRAINTS command.

--find constraint error 
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

Now we can fix the record in question, check the constraints again and then enable the constraint.

-- delete row in question
UPDATE [Person].[Address]
SET [StateProvinceID] = 79
WHERE AddressID = 1

-- check constraints again
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

-- try to enable constraint
ALTER TABLE [Person].[Address]
WITH CHECK CHECK CONSTRAINT FK_Address_StateProvince_StateProvinceID;

Example 2

Another example would be a check constraint on certain values that a column should store. This data could have been pre-populated and you add a new constraint with the NOCHECK option when you create the constraint or again this constraint could have been disabled for some reason.

In this example we create two check constraints that enforce the values for col1 and col2 to be greater than 100. The three rows that are created all violate the constraints for at least one of the columns.

-- create a new table
CREATE TABLE dbo.TestConstraint (col1 int, col2 int, col3 char (30)); -- insert some records
INSERT INTO dbo.TestConstraint VALUES (100, 100, 'Monday')
INSERT INTO dbo.TestConstraint VALUES (100, 99, 'Tuesday')
INSERT INTO dbo.TestConstraint VALUES (101, 98, 'Wednesday')

-- create check constraints with NOCHECK option
ALTER TABLE dbo.TestConstraint WITH NOCHECK 
ADD CONSTRAINT chkConstraint_1 CHECK (col1 > 100);

ALTER TABLE dbo.TestConstraint WITH NOCHECK 
ADD CONSTRAINT chkConstraint_2 CHECK (col2 > 100);

-- find the problems
DBCC CHECKCONSTRAINTS('dbo.TestConstraint') WITH ALL_CONSTRAINTS

When the DBCC CHECKCONSTRAINTS command is run, this is the output. From the data that we entered there should be three rows of data that should be returned. As you can see this command does not show you the actual records that are in violation, just the values and the columns that are in violation. Also, for the second insert values (100, 99) the 99 also violates the constraint, but this does not show up in the result set.

To get information about each constraint so we can see all of the values in violation we can run the following:

DBCC CHECKCONSTRAINTS('chkConstraint_1') 
DBCC CHECKCONSTRAINTS('chkConstraint_2')

At this point you would then need to write queries to find the exact records that are in violation and fix these records. The DBCC CHECKCONSTRAINTS command only shows the constraint that has been violated and the value, but not the primary key value for the record, so there is an additional step to find these records. To find these records you would do something like the following:

SELECT * FROM dbo.TestConstraint WHERE col1 = 100 

SELECT * FROM dbo.TestConstraint WHERE col2 IN (98,99,100)

Other Info

By default the DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS option should list all of the constraints that are in violation. As seen in Example 2 it only shows one issue per record and does not list all of them. So this could be a good place to start, but it may be helpful to do each constraint individually. To get a list of all of the constraints in your database you can issue the following command and then run the DBCC CHECKCONSTRAINTS command for each constraint.

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Next Steps

• Check out books online for additional info about DBCC CHECKCONSTRAINTS
• Keep in mind that SQL Server needs to read through all of your data to check for the constraints, so be careful not to run this across the board for your large or very busy databases. This should be something that is run off hours and also for very large databases you should run this either at a table level or a constraint level.
• Use this command to check your data integrity based on the rules that you have enforced.

About the author

Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips