/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 vote354 views2 answers
Mysql add column tinyint default 0
SQL
0 vote297 views1 answer
Mysql update sum same table
SQL
0 vote388 views2 answers
SQL SERVER ORDER BY DATE NULL LAST
SQL
31 votes312 views4 answers
How to get duplicate records with multiple field in sql
SQL
0 vote328 views1 answer
How to login to mysql in homestead
SQL
0 vote322 views1 answer
Postgres week number
SQL
1 vote346 views2 answers
Mysql extract number from string
SQL
0 vote314 views1 answer
Could not assemble any primary key columns for mapped table sqlalchemy
SQL
6 votes302 views1 answer
Allsource oracle
SQL
0 vote313 views1 answer
Oracle string substitution
SQL