/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

15 votes174 views4 answers
Mysql where value is null
SQL
0 vote308 views4 answers
Stored procedure sql
SQL
0 vote238 views2 answers
Mysql cast to varchar
SQL
1 vote196 views1 answer
Insert value to new table by joining 2 different tables
SQL
2 votes169 views1 answer
Create table with primary key
SQL
48 votes285 views3 answers
Psql create database
SQL
0 vote172 views1 answer
Changer un mot de passe mysql
SQL
3 votes186 views2 answers
Sql random integer
SQL
3 votes181 views1 answer
Sql insert inserted id
SQL
0 vote184 views1 answer
Sql server user permissions and roles
SQL