/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 vote334 views1 answer
Buscar nombre de columna en todas las tablas sql server
SQL
0 vote327 views1 answer
Insert ip address in mysql
SQL
-1 vote310 views2 answers
Sql convert date to string yyyy-mm-dd
SQL
1 vote283 views1 answer
Sql comment header
SQL
4 votes309 views2 answers
Oracle default date format
SQL
0 vote318 views1 answer
How we can perform acid Operations in sql with examples
SQL
0 vote298 views1 answer
Sql grant select only
SQL
0 vote300 views1 answer
Search In the Data using ObjectName
SQL
1 vote299 views1 answer
SQL server query column yes or no
SQL
1 vote337 views1 answer
Ora-01109 database not open in oracle 19c
SQL