/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 vote192 views1 answer
How to install mysql 8.0 windows service
SQL
0 vote283 views8 answers
Sql to c# linq converter online
SQL
32 votes194 views6 answers
Insert select
SQL
0 vote156 views1 answer
Delete all rows from table sqlite
SQL
0 vote183 views1 answer
View t-sql mail configuration
SQL
0 vote272 views1 answer
Insert into table with only identity column
SQL
0 vote258 views1 answer
Cartesian product sql
SQL
4 votes294 views1 answer
Select first and last row sql
SQL
3 votes193 views2 answers
Mysql create timestamp column
SQL
0 vote215 views1 answer
How to combine first and last nae into one columb sql
SQL