/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 vote119 views1 answer
Mostrar datos de tablas relacionadas mysql kjava
SQL
0 vote126 views1 answer
Sort by
SQL
6 votes118 views2 answers
Sql display max value
SQL
0 vote133 views1 answer
Error code 1451 sql
SQL
0 vote140 views1 answer
How to find unique element in sql
SQL
15 votes120 views3 answers
Postgresql change column type
SQL
0 vote120 views1 answer
Postgresql on conflict pair
SQL
3 votes124 views2 answers
Mysql user permission database
SQL
0 vote117 views1 answer
Sql server remove primary key without dropping table
SQL
1 vote125 views1 answer
Sql how to duplicate a table
SQL