/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

0 vote275 views1 answer
Laravel eloquent get generated sql
SQL
0 vote266 views1 answer
Postgresql update to unique
SQL
2 votes302 views1 answer
Mysql create user with remote access
SQL
5 votes299 views3 answers
DBMS_OUTPUT.PUT_LINE
SQL
0 vote287 views1 answer
Sqlite explain plan
SQL
1 vote280 views1 answer
Moodle query item quiz
SQL
0 vote309 views1 answer
Mysqldump: error:
SQL
0 vote321 views1 answer
Snowflake drop column
SQL
2 votes295 views1 answer
Sql percentage with % rounded down
SQL
0 vote274 views2 answers
Starting mysql service from mac
SQL