/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

4 votes212 views2 answers
Show all database inside postgresql
SQL
0 vote209 views1 answer
Ms sql filter all sympbol
SQL
0 vote206 views1 answer
How to execute stored output to csv files
SQL
0 vote198 views1 answer
Soql more than today
SQL
0 vote218 views1 answer
SqlMessage: "Table 'Friends' already exists",
SQL
0 vote218 views3 answers
Joins and views sql
SQL
7 votes212 views1 answer
Set sequence value oracle
SQL
1 vote211 views1 answer
Postgresql transaction discard all
SQL
2 votes205 views2 answers
How to check database username and password in postgresql
SQL
1 vote230 views1 answer
Sql row having max
SQL