Friday, June 17, 2016

How to disable foreign key constraint in sql server

Some times we need to delete unwanted data from the tables at that time foreign key will not allow deleting data from the table. Then Use below syntax for disabling/enabling foreign key temporarily
Method 1:-
-- Disable all table constraints

ALTER TABLE TableName NOCHECK CONSTRAINT ALL

-- Enable all table constraints

ALTER TABLE TableName WITH CHECK CHECK CONSTRAINT ALL

-- Disable single constraint

ALTER TABLE TableName NOCHECK CONSTRAINT Constraint

-- Enable single constraint

ALTER TABLE TableName WITH CHECK CHECK CONSTRAINT Constraint

Method 2:-

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"

-- enable all constraints

exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"