/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 vote428 views1 answer
Mariadb change column type
SQL
0 vote363 views1 answer
Set classpath for mysql-connector.jar
SQL
1 vote372 views1 answer
Sql server cast date dd/mm/yyyy
SQL
15 votes350 views3 answers
Find duplicate keys in mysql
SQL
0 vote310 views1 answer
How to set field to null in sql
SQL
0 vote343 views1 answer
T-sql check if data exists
SQL
0 vote357 views1 answer
Mysql regex select
SQL
2 votes432 views1 answer
Tcl in sql
SQL
0 vote402 views1 answer
Sqlcl format
SQL
0 vote376 views1 answer
Influxdb clone measurement
SQL