/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 vote318 views1 answer
Mysql remove tabs from string
SQL
0 vote362 views1 answer
Mysql update all record removing 2 hours from column
SQL
0 vote316 views1 answer
H2 auto increment and unique
SQL
0 vote309 views1 answer
What is the essence of SQL?
SQL
0 vote323 views1 answer
Groupby error in mysql
SQL
6 votes335 views2 answers
Find mysql version linux
SQL
0 vote345 views1 answer
Postgresql auto increment not working
SQL
0 vote337 views1 answer
Sql Top 5 sutradara dengan filem terbanyak
SQL
2 votes352 views1 answer
Oracle to_char number format percentage
SQL
1 vote341 views1 answer
Date_part mysql
SQL