/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 vote283 views1 answer
Get who is hired in month in sql
SQL
0 vote286 views2 answers
Django.db.utils.OperationalError: (1698, "Access denied for user 'root'@'localhost'")
SQL
3 votes264 views1 answer
Oracle rollback
SQL
1 vote304 views1 answer
Insert many to many sql
SQL
0 vote266 views1 answer
Order by with where clause in mysql
SQL
0 vote304 views1 answer
Insert into table with only identity column
SQL
5 votes252 views2 answers
Oracle to_timestamp
SQL
5 votes270 views3 answers
Mysql update field from one table to another
SQL
0 vote284 views2 answers
Drop tables from local table database postgres
SQL
1 vote289 views1 answer
Sqlite update from another table
SQL