How to Temporarily disable all foreign key constraints

 While working with MSSQL and Asp.net Core with Entity Framework, many times we have came across a situation where we have to migrate data from one database to newly created database. e.g. Copying all the data from Production DB to Testing DB. 

• Using Entity Framework Code First approach, we create database
• Once database is created, need to copy all the data from MSSQL (Generate script > Dataonly); create Database script for the data
• Now while running the script generated from PROD db to Test DB (there could me some modifications in test db, some columns are added) , we need to disable foreign key constraint and then re-enable foreign key constraints

 

How to disable all the foreign key constraints in MSSQL and then again re-enable all the foreign key constraints in MSSQL 

We can use following scripts

-- Disable all the constraint in database
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

-- Enable all the constraint in database
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

This is the most easiest way to Disable and Enable all the Foreign key constraints on MSSQL.