/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 vote340 views1 answer
Install sql server in ubuntu 20.04
SQL
0 vote336 views1 answer
Sql count more than 1
SQL
2 votes330 views1 answer
Postgresql drop primary key constraint
SQL
14 votes390 views4 answers
Sql compiler
SQL
1 vote335 views1 answer
Sample clause in sql
SQL
0 vote337 views1 answer
T sql check active deadlock
SQL
0 vote383 views1 answer
Mysql sum per week
SQL
13 votes539 views4 answers
Sql joining 3 tables
SQL
11 votes346 views2 answers
Insert current date sql
SQL
0 vote347 views2 answers
Functions with parameters SQL
SQL