Finding and fixing SQL Server database constraint issues

By:   |   Updated: 2023-05-19   |   Comments (7)   |   Related: More > 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
Msg 547, Level 16, State 0, Line 21
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Address_StateProvince_StateProvinceID". The conflict occurred in database "AdventureWorks2019", table "Person.StateProvince", column 'StateProvinceID'.

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.

Msg 547, Level 16, State 0, Line 26
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Address_StateProvince_StateProvinceID". The conflict occurred in database "AdventureWorks2019", table "Person.StateProvince", column 'StateProvinceID'.

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

--find constraint error 
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS
dbcc c1

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.

dbcc c2

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')
dbcc c3

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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux 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


Article Last Updated: 2023-05-19

Comments For This Article




Monday, October 2, 2023 - 6:20:39 AM - Dave Boltman Back To Top (91613)
Thanks for this useful article, Rob!

BTW - you and the official MS docs* say that WITH ALL_ERRORMSGS returns all rows that violate constraints. This seems to imply that it should show the actual records in the table that violate the check, but I haven't been able to get such an output even after a lot of research and experimentation. Then the text ALL_ERRORMSGS doesn't surface again in your article.

Are the docs ambiguous, or is anyone aware of a way to list the actual rows that do not satisfy the check condition?

Thanks,
Dave

Wednesday, July 31, 2019 - 10:59:57 AM - Flavien Rossignol Back To Top (81916)

I was looking for something like CHECKCONSTRAINTS without believing. Very useful !

Thanks a lot Greg !


Wednesday, May 2, 2018 - 5:08:20 AM - Pratik Back To Top (75837)

when I run this

dbcc checkconstraints

Why do I get only top 200 constraints and not all


Thursday, November 20, 2014 - 2:51:13 AM - Ashraf Back To Top (35347)

 

Hey - I just want to say THANKS! - I feel I know u cos, from all the google material researched I always rather use yr examples!! - Thanks Sir!


Tuesday, July 17, 2012 - 3:38:31 PM - Greg Robidoux Back To Top (18585)

See if this query gives you the info you are looking for.

select
SCHEMA_NAME(b.schema_id) as SchemaName,
b.name as TableName,
SCHEMA_NAME(b1.schema_id) as FKSchemaName,
b1.name as FKTableName,
a.name as FKName,
c.rows as Rows,
c1.rows as FKRows
from sys.foreign_keys a
inner join sys.objects b on a.parent_object_id = b.object_id
inner join sys.objects b1 on a.referenced_object_id = b1.object_id
inner join sys.partitions c on b.object_id = c.object_id
inner join sys.partitions c1 on a.referenced_object_id = c1.object_id
where c.index_id in (0,1) and c1.index_id in (0,1)


Friday, July 13, 2012 - 2:57:06 AM - bhagyashree patwardhan Back To Top (18488)

Can anyone help me to take count of no of rows affected by the FK constraint? I need the output like constraint name and the count.


Monday, January 31, 2011 - 9:23:49 AM - rahul Back To Top (12752)

check this link

http://www.mindstick.com/Articles/a09bf02e-fa00-4135-aeaa-c6ab6f49a827/?Constraint%20in%20SQL%20Server