Finding and fixing SQL Server database constraint issues

By:   |   Updated: 2007-05-11   |   Comments (6)   |   Related: More > Constraints

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?

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 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.

-- disable FK constraint
ALTER TABLE HumanResources.EmployeeAddress
NOCHECK CONSTRAINT FK_EmployeeAddress_Employee_EmployeeID;

-- insert new row
-- find current max EmployeeID
DECLARE @maxEmployeeID int
SELECT @maxEmployeeID = MAX(EmployeeID)
FROM HumanResources.Employee
-- add 1 to value
SET @maxEmployeeID = @maxEmployeeID + 1
-- insert new row
INSERT INTO HumanResources.EmployeeAddress
(employeeID, AddressID)
VALUES (@maxEmployeeID, 36)

-- try to enable constraint
ALTER TABLE HumanResources.EmployeeAddress
WITH CHECK CHECK CONSTRAINT FK_EmployeeAddress_Employee_EmployeeID;

When the constraint is enabled with the CHECK option, we get this error.

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

--find constraint error

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

-- delete row in question
DELETE FROM HumanResources.EmployeeAddress
WHERE employeeID = 291

-- check constraints again

-- try to enable constraint
ALTER TABLE HumanResources.EmployeeAddress
WITH CHECK CHECK CONSTRAINT FK_EmployeeAddress_Employee_EmployeeID;

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
ADD CONSTRAINT chkConstraint_1 CHECK (col1 > 100);

ADD CONSTRAINT chkConstraint_2 CHECK (col2 > 100);

-- find the problems

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:


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.


Next Steps

  • Check out books online for additional info about DBCC CHECKCONSTRAINTS (2005) and DBCC CHECKCONSTRAINTS (2000)
  • 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.

Last Updated: 2007-05-11

get scripts

next tip button

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of

View all my tips

Comments For This Article

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 02, 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.

SCHEMA_NAME(b.schema_id) as SchemaName, as TableName,
SCHEMA_NAME(b1.schema_id) as FKSchemaName, as FKTableName, 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





Recommended Reading

SQL Server Foreign Key Update and Delete Rules

Truncate all tables in a SQL Server database

Drop and Re-Create All Foreign Key Constraints in SQL Server

Script to Delete Data from SQL Server Tables with Foreign Key Constraints

How to create a SQL Server foreign key

get free sql tips
agree to terms