You can use the code below to enable all CHECK and foreign key constraints for a specific table in SQL Server.
When you enable a constraint in SQL Server, you need to decide whether it should check any existing data or not. This is an important consideration if the table already contains data, because that existing data may potentially violate the constraint’s rules.
Sample Statements
Below are two sample statements that show the difference between checking existing data and not checking it when you enable the constraints.
To check existing data, use WITH CHECK in your statement when enabling the constraints, otherwise use WITH NOCHECK.
With Check:
ALTER TABLE TableName WITH CHECK CHECK CONSTRAINT ALL
With No Check:
ALTER TABLE TableName WITH NOCHECK CHECK CONSTRAINT ALL
Just replace TableName with the name of the applicable table.
Below is an example where I do this and check the results.
Example 1 – Review the Constraints
First, I’ll take a quick look at the current CHECK constraints in the database, to see whether or not they are enabled or disabled.
SELECT OBJECT_NAME(parent_object_id) AS 'Table', name AS 'Constraint', is_disabled, is_not_trusted FROM sys.foreign_keys UNION SELECT OBJECT_NAME(parent_object_id), name, is_disabled, is_not_trusted FROM sys.check_constraints;
Result:
+----------------+-----------------+---------------+------------------+ | Table | Constraint | is_disabled | is_not_trusted | |----------------+-----------------+---------------+------------------| | ConstraintTest | chkPrice | 1 | 1 | | ConstraintTest | chkValidEndDate | 1 | 1 | | ConstraintTest | chkTeamSize | 1 | 1 | | Occupation | chkJobTitle | 0 | 0 | +----------------+-----------------+---------------+------------------+
So there are currently four CHECK constraints in the database, three of which are for the ConstraintTest table.
We can see that all constraints for the table are disabled because is_disabled is set to 1.
We can also see that the is_not_trusted column is set to 1 for these constraints. This means that they’re not trusted. They can’t be trusted while they’re disabled, because invalid data can enter the database without being checked. More on this below.
The other constraint (for another table) is already enabled and trusted (but we can ignore that table/constraint in this article).
Example 2 – Enable the Constraints using WITH CHECK
Now I’ll enable all constraints for the ConstraintTest table:
ALTER TABLE ConstraintTest WITH CHECK CHECK CONSTRAINT ALL;
That’s it – done.
Now let’s run the same query from the first example to see the result.
SELECT OBJECT_NAME(parent_object_id) AS 'Table', name AS 'Constraint', is_disabled, is_not_trusted FROM sys.foreign_keys UNION SELECT OBJECT_NAME(parent_object_id), name, is_disabled, is_not_trusted FROM sys.check_constraints;
Result:
+----------------+-----------------+---------------+------------------+ | Table | Constraint | is_disabled | is_not_trusted | |----------------+-----------------+---------------+------------------| | ConstraintTest | chkPrice | 0 | 0 | | ConstraintTest | chkValidEndDate | 0 | 0 | | ConstraintTest | chkTeamSize | 0 | 0 | | Occupation | chkJobTitle | 0 | 0 | +----------------+-----------------+---------------+------------------+
All three constraints for the ConstraintTest table are now enabled and trusted.
They are trusted because I used WITH CHECK in my statement. If I hadn’t, I would’ve got a different result, as you’ll see below.
By using WITH CHECK, I can be sure that any existing data in the table does in fact conform to the constraints.
Example 3 – Enable the Constraints using WITH NOCHECK
Now I’ll re-enable the constraints using WITH CHECK so we can see how this affects the outcome.
But first I’ll need to disable them:
ALTER TABLE ConstraintTest NOCHECK CONSTRAINT ALL;
Check that they’re disabled:
SELECT OBJECT_NAME(parent_object_id) AS 'Table', name AS 'Constraint', is_disabled, is_not_trusted FROM sys.foreign_keys UNION SELECT OBJECT_NAME(parent_object_id), name, is_disabled, is_not_trusted FROM sys.check_constraints;
Result:
+----------------+-----------------+---------------+------------------+ | Table | Constraint | is_disabled | is_not_trusted | |----------------+-----------------+---------------+------------------| | ConstraintTest | chkPrice | 1 | 1 | | ConstraintTest | chkValidEndDate | 1 | 1 | | ConstraintTest | chkTeamSize | 1 | 1 | | Occupation | chkJobTitle | 0 | 0 | +----------------+-----------------+---------------+------------------+
So they’re disabled once again.
Now re-enable them using WITH NOCHECK:
ALTER TABLE ConstraintTest WITH NOCHECK CHECK CONSTRAINT ALL;
Check again:
SELECT OBJECT_NAME(parent_object_id) AS 'Table', name AS 'Constraint', is_disabled, is_not_trusted FROM sys.foreign_keys UNION SELECT OBJECT_NAME(parent_object_id), name, is_disabled, is_not_trusted FROM sys.check_constraints;
Result:
+----------------+-----------------+---------------+------------------+ | Table | Constraint | is_disabled | is_not_trusted | |----------------+-----------------+---------------+------------------| | ConstraintTest | chkPrice | 0 | 1 | | ConstraintTest | chkValidEndDate | 0 | 1 | | ConstraintTest | chkTeamSize | 0 | 1 | | Occupation | chkJobTitle | 0 | 0 | +----------------+-----------------+---------------+------------------+
We can see that the constraints have been successfully enabled, but this time they remain untrusted.
The constraints are untrusted because they didn’t check any existing data before being enabled.
So the key takeaway here is, if you want your constraints to be trusted, be sure to use WITH CHECK when enabling them.
Enabling Constraints Individually
If you don’t want to enable all constraints at once, you can enable them individually. This can also be useful if you do need to enable all constraints, but you need to use different settings (e.g. WITH CHECK for some and WITH NOCHECK for others).
See How to Enable a CHECK Constraint in SQL Server and How to Enable a Foreign Key in SQL Server.