/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 vote181 views1 answer
SQL SELECT-Klausel
SQL
0 vote159 views1 answer
Couldn't execute 'SELECT COLUMN_NAME
SQL
0 vote263 views2 answers
Sqlalchemy default value for column
SQL
2 votes185 views1 answer
Add default sql server
SQL
0 vote573 views2 answers
Zsh: command not found: mysq
SQL
10 votes196 views4 answers
SELECT FROM WHERE
SQL
0 vote195 views1 answer
How to make case insensitive in sql
SQL
1 vote196 views3 answers
Rows, INSERT INTO
SQL
0 vote208 views1 answer
Bigrock database connection string
SQL
0 vote207 views1 answer
Oracle string length
SQL