/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

2 votes143 views5 answers
Eloquest how to select one specific column in database
SQL
2 votes147 views2 answers
How to run postgresql in linux
SQL
0 vote138 views1 answer
How to print records of employees hired in particular month in oracle
SQL
0 vote139 views1 answer
Failed to process SQL command - ORA-28014: cannot drop administrative user or role
SQL
4 votes124 views2 answers
Sql server date now
SQL
24 votes135 views5 answers
Sql server insert into select
SQL
0 vote133 views4 answers
Database stuck at restoring state
SQL
1 vote147 views1 answer
Oracle create index if not exists
SQL
0 vote128 views2 answers
Sqlite unique
SQL
20 votes132 views6 answers
Change password in mysql
SQL