/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 votes139 views1 answer
Sql add column after another
SQL
0 vote135 views1 answer
Mysql select where field like in list
SQL
0 vote157 views1 answer
Authentication modes in sql server
SQL
2 votes154 views1 answer
Grant revoke privileges to mysql username
SQL
0 vote139 views1 answer
Flush user resource mysql
SQL
10 votes145 views3 answers
Sql check double value
SQL
5 votes149 views2 answers
Oracle show procedures
SQL
-1 vote148 views2 answers
Installing mysql on centos 7
SQL
0 vote158 views2 answers
How to see table associated with a schema in sql
SQL
1 vote190 views1 answer
Sql comment header
SQL