/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

-1 vote103 views1 answer
Can pandas fetch data from sql
SQL
0 vote105 views1 answer
Get employees if not contains in sql
SQL
0 vote107 views1 answer
Sql count null values in all columns
SQL
1 vote104 views2 answers
T-sql random number for each row
SQL
0 vote177 views1 answer
"inspanel" intitle:"login" -"cannot" "Login ID" -site:luckyfish.io
SQL
22 votes131 views3 answers
Sql cheat sheet
SQL
2 votes94 views1 answer
Oracle create directory
SQL
2 votes129 views1 answer
Merge two columns in sql oracle
SQL
0 vote95 views2 answers
Insert into
SQL
4 votes99 views1 answer
Sql any
SQL