/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

7 votes129 views3 answers
Python and mysql connectivity
SQL
0 vote121 views1 answer
Create a table from one field of another table
SQL
7 votes125 views4 answers
How to reset the identity column in sql server
SQL
0 vote146 views1 answer
Calculer pourcentage mysql
SQL
1 vote126 views1 answer
Sql delete all values in a column
SQL
0 vote122 views1 answer
Mysql create table like
SQL
0 vote120 views1 answer
Mysql does collate nopad recognize space
SQL
1 vote108 views2 answers
Sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unable to open database file
SQL
1 vote117 views4 answers
Sequelize migration default value
SQL
0 vote156 views1 answer
Day of the week sqlite
SQL