/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 vote247 views1 answer
Mysql disable triggers
SQL
0 vote185 views2 answers
Sql server select first day of previous year
SQL
10 votes171 views2 answers
Mysql alter add foreign key
SQL
20 votes186 views6 answers
Mysql reset auto increment
SQL
13 votes193 views1 answer
Sql join 3 tables on id
SQL
0 vote185 views1 answer
Mysql url data type
SQL
0 vote177 views3 answers
Between keyword sql
SQL
5 votes170 views3 answers
How to remove unique key constraint in mysql
SQL
15 votes175 views6 answers
Psql
SQL
5 votes253 views4 answers
Restart the psql server windows
SQL