/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 vote125 views1 answer
Print year of a date sql
SQL
0 vote74 views1 answer
Get all stored procedures in sql server
SQL
0 vote119 views2 answers
How to save result of sqlite query in a table
SQL
0 vote109 views1 answer
Get who is hired in specific month in sql
SQL
1 vote107 views1 answer
Rows to comma separated values in mssql
SQL
0 vote186 views1 answer
Trigger
SQL
2 votes123 views2 answers
Microsoft sql server extract hour and minute from datetime
SQL
1 vote124 views1 answer
Mysql month name extract
SQL
2 votes117 views1 answer
Mysql ALTER TABLE ADD COLUMN BOOLEAN AFTER DEFAULT "1";
SQL
0 vote171 views1 answer
Sql ssrs
SQL