/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 vote237 views1 answer
Sql server information_schema temp tables
SQL
0 vote241 views1 answer
Normalization in database 2nd normal form
SQL
0 vote231 views1 answer
Default value false mysql
SQL
0 vote229 views1 answer
Oracle sqlp update amount / quantity
SQL
0 vote251 views1 answer
Migration graphql
SQL
0 vote233 views1 answer
Sql "List the contact methods found in the Contact table.For each contact method, list how many people chose that contact method."
SQL
2 votes251 views2 answers
Mysql search for column name in all tables
SQL
0 vote241 views1 answer
How to to get time in sql
SQL
0 vote230 views1 answer
Error code 1215 cannot add foreign key constraint
SQL
0 vote310 views1 answer
Delete ids between sql
SQL