/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 vote208 views2 answers
Find Overlapping sql
SQL
40 votes253 views4 answers
PostgreSQL add column
SQL
3 votes224 views2 answers
Get current year sql
SQL
0 vote235 views1 answer
No query unable to fetch row sqlite
SQL
13 votes206 views5 answers
Oracle list tables
SQL
1 vote224 views3 answers
Connect to mysql c# connection string C#
SQL
3 votes229 views1 answer
Sql print all names that start with a given letter
SQL
0 vote228 views1 answer
Shortcut run sql pgadmin
SQL
1 vote289 views2 answers
Coderbyte sql solutions
SQL
0 vote167 views1 answer
Java spring boot connect to sql server
SQL