/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

5 votes331 views1 answer
Oracle list functions
SQL
0 vote331 views1 answer
Ubuntu connect to mssql database
SQL
0 vote304 views1 answer
MySql count occurences more than"
SQL
11 votes321 views4 answers
Query DISTINCT
SQL
0 vote308 views1 answer
Stop psql server windows
SQL
0 vote327 views1 answer
Sql server list user permissions
SQL
6 votes382 views4 answers
Mysql get random row
SQL
0 vote316 views1 answer
Mysql jdbc timezone
SQL
0 vote317 views1 answer
SqlFunction does not exist
SQL
0 vote326 views1 answer
Function sum(text) does not exist postgres
SQL