/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 vote250 views2 answers
Postgresql get last day of month
SQL
0 vote265 views3 answers
Joins and views sql
SQL
0 vote247 views1 answer
Flask sqlalchemy update row
SQL
7 votes289 views2 answers
Sql remove duplicates based on column
SQL
6 votes264 views3 answers
Commit in sql
SQL
1 vote274 views1 answer
Sql count value greater than
SQL
0 vote267 views1 answer
Sql insert query list of dictionaries
SQL
14 votes275 views1 answer
Oracle alter table add column not null
SQL
0 vote266 views1 answer
Hotw to alter lengh character vayng postgres
SQL
0 vote270 views1 answer
Desinstaller mysql sur ubuntu definitivement
SQL