/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 vote157 views1 answer
Name of today sql
SQL
18 votes153 views3 answers
'Client does not support authentication protocol requested by server; consider upgrading MySQL client', sqlState: '08004',
SQL
0 vote154 views1 answer
Use mysql in java program
SQL
0 vote151 views2 answers
Postgresql get last day of month
SQL
0 vote142 views1 answer
Get number of rows in every table mysql
SQL
1 vote145 views1 answer
Mysql best tutorial for beginners
SQL
0 vote142 views1 answer
What is the essence of SQL?
SQL
0 vote126 views2 answers
Postgresql display subquery as json
SQL
0 vote150 views1 answer
Mysql grant grant option
SQL
0 vote155 views3 answers
Java hide mysql login credentials
SQL