/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

3 votes139 views2 answers
Host is not allowed to connect to this mysql server
SQL
8 votes145 views1 answer
Oracle search stored procedures for text
SQL
7 votes132 views2 answers
Html to react converter
SQL
0 vote134 views1 answer
How to remove default in mysql
SQL
0 vote148 views2 answers
Impala rename column name
SQL
1 vote116 views1 answer
Foreign key constraint in ms sql
SQL
0 vote136 views2 answers
How to count codition
SQL
0 vote122 views1 answer
Test connection to sql server
SQL
0 vote123 views1 answer
Initcap in sql
SQL
39 votes197 views4 answers
Select duplicates in sql
SQL