/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

2 votes281 views1 answer
Sql server find table name
SQL
0 vote276 views1 answer
Duplicate value unique constraint mysql error
SQL
0 vote312 views1 answer
Learnxinyminutes sql
SQL
1 vote275 views1 answer
Oracle lock user
SQL
0 vote285 views1 answer
Oracle activate program
SQL
0 vote300 views1 answer
Mysql query with sql to get the next row
SQL
0 vote280 views1 answer
Oracle db get table sizes
SQL
0 vote308 views1 answer
Postgresql like
SQL
0 vote296 views1 answer
Get date ISO in psql
SQL
0 vote296 views1 answer
Ceil upto 2 decimal place mysql
SQL