Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Finding and fixing SQL Server database constraint issues


By:   |   Updated: 2007-05-11   |   Comments (6)   |   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 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
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
DELETE FROM HumanResources.EmployeeAddress
WHERE employeeID = 291

-- check constraints again
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

-- 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
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 (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 MSSQLTips.com.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

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

 

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

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

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

check this link

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

 

 

 


Learn more about SQL Server tools