/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 vote201 views1 answer
Mysql query single row
SQL
0 vote196 views1 answer
Get who is hired in february in sql
SQL
0 vote209 views2 answers
Amazon linux postgresql client
SQL
2 votes231 views1 answer
Pandas to sql index
SQL
1 vote194 views1 answer
Mysql select another database
SQL
0 vote191 views1 answer
Bigquery declare
SQL
1 vote136 views1 answer
Insert into sql
SQL
0 vote224 views1 answer
Postgres get database encoding
SQL
0 vote189 views1 answer
Fix sqlite3 ruby on rails
SQL
0 vote180 views1 answer
Mysql find foreign key references
SQL