/SQL/Mssql disable foreign key constraint

Mssql disable foreign key constraint

If you want to disable all constraints in the database just run this code:

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
To switch them back on, run: (the print is optional of course and it is just listing the tables)

-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
I find it useful when populating data from one database to another. It is much better approach than dropping constraints. As you mentioned it comes handy when dropping all the data in the database and repopulating it (say in test environment).

If you are deleting all the data you may find this solution to be helpful.

Also sometimes it is handy to disable all triggers as well, you can see the complete solution here.

Similar codes

0 vote165 views1 answer
Oracle procedure chain step
SQL
2 votes186 views1 answer
Power query concatenate
SQL
0 vote138 views0 answer
SQL
4 votes231 views4 answers
Check constraint in ms sql
SQL
3 votes151 views1 answer
Mysql CURRENT_TIMESTAMP()
SQL
0 vote178 views1 answer
Oracle locked objects
SQL
1 vote180 views1 answer
Where to locate set password for mysql
SQL
0 vote156 views1 answer
How to import sqlite driver class in java using maven
SQL
0 vote172 views1 answer
Select list is not in group by clause and contains nonaggregated column codeigniter
SQL
2 votes173 views2 answers
Maximum execution time of 360 seconds exceeded
SQL