/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

9 votes293 views3 answers
Sql delete where in
SQL
0 vote267 views1 answer
How to combine rows in sql server procedure
SQL
1 vote265 views2 answers
Check if word is in column sql
SQL
27 votes323 views3 answers
Mysql delete row
SQL
0 vote268 views1 answer
Template MySQL Zabbix agent
SQL
39 votes328 views4 answers
Select duplicates in sql
SQL
8 votes293 views4 answers
Reset SQL auto increment
SQL
18 votes266 views3 answers
Find column name in database
SQL
0 vote260 views1 answer
View to table
SQL
0 vote271 views1 answer
Install squirrel sql ubuntu
SQL