/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 vote143 views1 answer
Groupby sort sql hive
SQL
0 vote143 views1 answer
T-SQL Retry (deadlock)
SQL
1 vote91 views1 answer
Insert into sql
SQL
8 votes147 views2 answers
Alert table name mysql
SQL
0 vote154 views2 answers
Sql 2 way of select unique
SQL
1 vote141 views3 answers
Read all columns of a table sql
SQL
0 vote138 views1 answer
Nueva tabla mysql
SQL
1 vote142 views1 answer
How to select all attributes from a row if there is a certain string in it MySQL
SQL
9 votes145 views6 answers
Sql add column with default value
SQL
1 vote135 views2 answers
Sql for date greater than
SQL