To return a list of all untrusted foreign key constraints in a SQL Server database, you can run the T-SQL code below.
An untrusted foreign key is one that has its is_not_trusted
flag set to 1
.
To return a list of all untrusted foreign key constraints in a SQL Server database, you can run the T-SQL code below.
An untrusted foreign key is one that has its is_not_trusted
flag set to 1
.
If you need to return a list of all foreign key constraints that have been disabled in a SQL Server database, you can run the T-SQL code below.
If you find yourself in the situation where you need to disable a foreign key constraint in SQL Server, here’s how to do that using Transact-SQL.
This will allow you to enter data without being restricted by the foreign key. Obviously, you wouldn’t do this unless you had a very good reason to do so. Foreign keys enforce referential integrity, so disabling them has the potential to create all sorts of issues.
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
.
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.
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.
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
.
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.
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.
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.