/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 vote324 views1 answer
Define a variable in mysql from select
SQL
0 vote306 views1 answer
Restore backup mysql .gz
SQL
7 votes298 views4 answers
Mysql delete older duplicates
SQL
1 vote316 views1 answer
Where not in array sql
SQL
1 vote343 views1 answer
Create email address from first and last name in sql
SQL
1 vote250 views1 answer
Strict in postgres SQL
SQL
0 vote299 views1 answer
Mysql multiple count
SQL
0 vote295 views1 answer
Mysql create pool
SQL
0 vote286 views1 answer
Mysql concat and use as where column
SQL
1 vote309 views1 answer
Oracle datafile max size 32gb
SQL