If you need to return a list of all untrusted CHECK constraints in a SQL Server database, you can run the T-SQL code below.
By “untrusted”, I’m referring to those constraints that have their is_not_trusted flag set to 1.
Tag: mssql
How to Return All Disabled CHECK Constraints in SQL Server (T-SQL Example)
If you need to return a list of all CHECK constraints that have been disabled in a SQL Server database, you can run the T-SQL code below.
How to Disable All CHECK & Foreign Key Constraints in a Database in SQL Server (T-SQL Examples)
You can use the code below to disable all CHECK and foreign key constraints for the current database in SQL Server.
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
This uses Microsoft’s undocumented sp_MSforeachtable stored procedure. This procedure allows you to perform tasks against each table in a database. So it’s perfect for our task here – to disable all CHECK constraints within the current database.
Below is an example where I do this and then check the result.
How to Enable All CHECK & Foreign Key Constraints in a Database in SQL Server (T-SQL Examples)
You can use the code below to enable all CHECK and foreign key constraints for the current database in SQL Server.
When you enable a CHECK or foreign key constraint, you have the option of checking existing data in the table before the constraint is enabled. Doing this allows you to verify whether or not any existing violates the constraint. To perform this check, use WITH CHECK within the code, otherwise use WITH NOCHECK.
How to Disable All CHECK & Foreign Key Constraints for a Table in SQL Server (T-SQL Examples)
You can use the code below to disable all CHECK and foreign key constraints for a specific table in SQL Server.
Just replace TableName with the name of the applicable table.
ALTER TABLE TableName NOCHECK CONSTRAINT ALL
Below is an example where I do this and then check the result.
How to Enable All CHECK & Foreign Key Constraints for a Table in SQL Server (T-SQL Examples)
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.
How to Disable a CHECK Constraint in SQL Server (T-SQL Examples)
When you attempt to enter data into a table that has a fully enabled CHECK constraint, you will only be successful if the data doesn’t violate that constraint. If you attempt to enter invalid data, the operation will fail with an error.
But what if you find yourself in the situation where you really must insert data that will violate the CHECK constraint? Perhaps the constraint no longer applies, or maybe you have an exception where one row is allowed to bypass the constraint. Either way, you won’t be able to enter anything outside the rules of the constraint.
If you find yourself in this situation, you can always disable the constraint. Here’s how to do that using Transact-SQL.
How to Enable a CHECK Constraint in SQL Server (T-SQL Example)
If you have a CHECK constraint in SQL Server that is currently disabled, you can use the code below to re-enable it.
When you enable a CHECK constraint (or a foreign key constraint for that matter), you have the option to specify whether or not to check any existing data in the table.
Below are code examples of enabling a CHECK constraint, while specifying each of these different options.
What You Should Know about WITH NOCHECK when Enabling a CHECK Constraint in SQL Server
If you ever find yourself in the situation where you need to re-enable a CHECK constraint that has previously been disabled, you should definitely make sure that you know what you’re doing.
In particular, you should understand the difference between WITH NOCHECK and WITH CHECK arguments.
These arguments can be used at the time you enable the constraint. They specify whether or not existing data is validated against your re-enabled (or newly added) CHECK constraint. Basically you have the option of checking all existing data for any violations against the constraint. If you don’t specify anything, existing data won’t be checked. That’s why it’s important to understand how it works.
By the way, these arguments also apply to foreign key constraints.
Find Out if a CHECK Constraint is Column-Level or Table-Level in SQL Server (T-SQL Examples)
When you create a CHECK constraint in SQL Server, you might not even think about whether it’s a table-level constraint or a column-level constraint.
A table-level CHECK constraint applies to the table, whereas a column-level constraint applies to a specific column. With a table-level CHECK constraint, it’s the row that is checked when it checks the data. With a column-level CHECK constraint, it’s the specific column that is checked.
Generally you’ll know whether or not the constraint you’re creating is a table-level or column-level constraint by the definition you give it. If only one column is being checked in the expression, it will be a column-level constraint. Otherwise it will be a table-level constraint.
But how do you know if your existing constraints are column-level or table-level?
You can run any of the code examples below to determine whether your existing constraints are column-level or table-level. These retrieve all CHECK constraints for the current database, but you can always use a WHERE clause to narrow it down to a specific constraint.