One of the most popular questions I find still coming to via email is how to enable or disable all the constraint for single table or database.
Well, in this blog post we will not discuss the reasons why do you need them or what are the advantages or disadvantages of the same. Instead, we will go over scripts to do the same. As lots of users are seeking this script there should be good reason for the same.
-- Disable all table constraints
ALTER TABLE YourTableName NOCHECK CONSTRAINT ALL
-- Enable all table constraints
ALTER TABLE YourTableName CHECK CONSTRAINT ALL
-- ----------
-- Disable single constraint
ALTER TABLE YourTableName NOCHECK CONSTRAINT YourConstraint
-- Enable single constraint
ALTER TABLE YourTableName CHECK CONSTRAINT YourConstraint
-- ----------
-- Disable all constraints for database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
-- Enable all constraints for database
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
Let me know if there is any better way to do the same.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
16 Comments. Leave new
Hi,
good one.
There is a slide typo error on a statement for enabling all constraints for a database.
Regards,
Hitesh Shah
The foreachproc is a handy tool, but it doesnt work on SQL AZURE, so I came up with a script to generate the ENABLE/DISABLE constraints for all the database.
SELECT ‘ALTER TABLE [‘ + s.name + ‘].[‘ + o.name + ‘] WITH CHECK CHECK CONSTRAINT [‘ + i.name + ‘]’
–SELECT ‘ALTER TABLE [‘ + s.name + ‘].[‘ + o.name + ‘] NOCHECK CONSTRAINT all’
from sys.foreign_keys i
INNER JOIN sys.objects o ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
thanks for sharing it ribeirofloripa!
Useful info, Many thanks for the share…
thank you
but the problem after execute istructions
ALTER TABLE YourTableName NOCHECK CONSTRAINT ALL
and
ALTER TABLE YourTableName CHECK CONSTRAINT ALL
for all DB tables i loss all relations SCHEMA(s) :-(
DB constraints still valid but i can’t find anymore schemas with SQL Server Management Studio!!!
betweene the two istructions i delete the content of all tables
Thank you, that snippet was exactly what I needed today :-)
i do this in situations where i have to copy data from one table to another having a foreign key.
to bypass the foreign key constraint
maroona – thanks for sharing.
“Add related tables” in database diagram is grayed out. I have enabled all constraints but no result.
This should be used with caution – I ran this on a database on which someone had disabled constraints in order to insert test data. Needless to say the recovery was not pleasant. :)
Ruairi – I should put a red flashing light saying “Caution!”
Dave, thanks for the script. Question: Why the inconsistency on the WITH CHECK in the re-enable for your three sets of scripts? Don’t the first two cause me to lose my WITH CHECK?
You must double check in order to actually tell sql server to trust the constraint, otherwise the constraint is ignored for query plans. Simply enabling is not enough.
Alter table SalesOrderdetail WITH CHECK check constraint FTCustomerID; — notice the double check
@pinal firstly, thanks for your scripts, very handy for my use case!
for this line
— Enable all table constraints
ALTER TABLE YourTableName CHECK CONSTRAINT ALL
I modified to
ALTER TABLE YourTableName WITH CHECK CHECK CONSTRAINT ALL
I realised that the NOCHECK was still applied in the table definition (but FK definition was fine) and found this note:
“The query optimizer doesn’t consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.”
from https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver15
how to check if a constraint is enabled or disabled ?