DBCC CHECKCONSTRAINTS checks the consistency of a specified constraint, or all constraints in a table.
So what is Constraint?
Constraints are the restrictions set for the data in the database. There are several constraint types. Below you will find articles related to these Constraints.
| NOT NULL | The constraint that prevents the column from being null. |
| UNIQUE CONSTRAINT | You can find the details in my article “What is Unique Constraint“. |
| PRIMARY KEY | You can find it in the article “What is Primary Key and Foreign Key“. |
| FOREIGN KEY | You can find it in the article “What is Primary Key and Foreign Key“. |
| CHECK CONSTRAINT | You can find it in the article “How To Create CHECK CONSTRAINT“. |
| DEFAULT CONSTRAINT | You can find it in the article “How To Create Default Constraint On SQL Server“ |
We may need to resolve the inconsistency in the database by using some of the commands that start with DBCC CHECK when an inconsistency occurs on the database. Fixing the problem in this way can sometimes create problems with the constraints. So after running these commands, we can check the consistency of the constraints with the following command.
|
1 2 3 |
USE Test GO DBCC CHECKCONSTRAINTS (N'TestTable') WITH NO_INFOMSGS, ALL_ERRORMSGS; |
The command we are running above controls the consistency of the only active constraints. When you add ALL_CONSTRAINTS to the script as below, it will check all active and passive constraints.
|
1 2 3 |
USE Test GO DBCC CHECKCONSTRAINTS (N'TestTable') WITH ALL_CONSTRAINTS,NO_INFOMSGS, ALL_ERRORMSGS; |
The command we are running above controls the consistency of only active constraints. When you add ALL_CONSTRAINTS to the script as below, it will check all active and passive constraints.
|
1 2 3 |
USE Test GO DBCC CHECKCONSTRAINTS (N'ConstraintExample') WITH NO_INFOMSGS, ALL_ERRORMSGS; |
