/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 votes332 views3 answers
Mysql list tables by size
SQL
0 vote351 views1 answer
Oracle sql date winter time
SQL
7 votes349 views1 answer
Sql server convert string to date
SQL
14 votes353 views3 answers
Oracle sql limit results
SQL
0 vote347 views1 answer
Select even number id data from table
SQL
4 votes354 views3 answers
How to describe a table in sqlite3
SQL
0 vote360 views1 answer
Oracle step procedure
SQL
2 votes372 views1 answer
Oracle to_char number format percentage
SQL
0 vote377 views1 answer
Sql with example
SQL
0 vote363 views1 answer
Mysql match in serialized data
SQL