/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 vote209 views1 answer
Python connect to mysql in settings.py
SQL
12 votes209 views4 answers
How to check mysql version ubuntu
SQL
3 votes216 views4 answers
Postgresql change timezone on timestamp without timezone
SQL
0 vote188 views1 answer
RowDataPacket
SQL
-1 vote213 views5 answers
Operator in sql
SQL
5 votes224 views1 answer
Mysql count newlines in field
SQL
13 votes193 views4 answers
Mysql get last row
SQL
1 vote274 views1 answer
Top 10 rows in mysql
SQL
0 vote189 views1 answer
Check sql database table lock
SQL
1 vote249 views1 answer
Sqlalchemy on delete set null
SQL