/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 vote273 views1 answer
Do you know sql
SQL
0 vote181 views1 answer
Remove from table where
SQL
0 vote151 views1 answer
Mysql timeout 30 seconds
SQL
0 vote218 views2 answers
Clauses in mysql
SQL
0 vote696 views1 answer
T sql less than date
SQL
3 votes204 views1 answer
Start mysql server by terminal in linux
SQL
0 vote249 views1 answer
How many rows affected in sqllite
SQL
13 votes182 views2 answers
Oracle select into and inner join
SQL
8 votes245 views2 answers
Python simple connect to mysql
SQL
0 vote278 views1 answer
Contraint default SQL
SQL