/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 vote233 views1 answer
Rermove categories woocommerce sql
SQL
0 vote217 views1 answer
How to count with except in psql
SQL
0 vote225 views3 answers
Get free card roblox
SQL
6 votes269 views2 answers
Mysql convert timestamp to date
SQL
0 vote216 views1 answer
Dns slave zone convert
SQL
0 vote211 views1 answer
Dump only schema
SQL
2 votes180 views1 answer
Sql float to int
SQL
6 votes230 views3 answers
POSTGRES INSERT INTO TABLE VALUE FROM OTHER TABLE
SQL
0 vote222 views2 answers
2nd max salary query in sql
SQL
0 vote241 views1 answer
Insert data from stored procedure into table
SQL