/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

6 votes207 views3 answers
Mysql create database utf8
SQL
0 vote190 views1 answer
Alter table fulltext
SQL
0 vote205 views1 answer
Mysql a from on this page has
SQL
10 votes193 views1 answer
Oracle object dependencies
SQL
0 vote202 views1 answer
Select row with latest date mysql
SQL
1 vote184 views1 answer
Using SQL in rails migration
SQL
2 votes174 views1 answer
Oracle sql drop column if exists
SQL
0 vote224 views1 answer
Mysql streaming example
SQL
5 votes204 views2 answers
Mysqldump password
SQL
0 vote199 views1 answer
On sql table data exists
SQL