/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 vote210 views1 answer
Is there a way to use read_sql_query and the query has WHERE column = Variable?
SQL
1 vote209 views1 answer
Complete output (15 lines): /bin/sh: 1: mysql_config: not found /bin/sh: 1: mariadb_config: not found /bin/sh: 1: mysql_config: not found Traceback (most recent call last): File "<string>", line 1, in <module> File "/tmp/pi
SQL
0 vote200 views1 answer
How to print mysql query of codeigniter query builder
SQL
4 votes210 views6 answers
List all tables in postgres
SQL
7 votes204 views1 answer
Mysql remote connection command line
SQL
1 vote279 views1 answer
How to get alternate records from a table in sql
SQL
11 votes204 views6 answers
Distinct sql
SQL
0 vote196 views2 answers
Find Overlapping sql
SQL
0 vote238 views1 answer
Convert LDAP Epoch to Date
SQL
1 vote204 views1 answer
Sql how to duplicate a table
SQL