How to turn off/on all the foreign keys and triggers in Microsoft SQL Server?

Question

It can be needed for inserting and modifying of big amounts of data.

This may be helpful in globally disabling all foreign key constraints: http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql — Klazen108, Nov 01 '13 at 14:03
You should use `ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL` (note the additional `WITH CHECK` option) otherwise existing data in the table will *not* be verified when turning on the constraints. This is extremely dangerous when you turned them off for bulk loading, the loaded data will not be verified without the `WITH CHECK` option! — , Nov 01 '13 at 14:27

score 12 · Accepted Answer · answered Nov 01 '13 at 14:15

Disable all FKs:

EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
GO

Enable all FKs:

EXEC sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"
GO

Disable all triggers:

EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"
GO

Enable all triggers:

EXEC sp_MSforeachtable @command1="ALTER TABLE ? ENABLE TRIGGER ALL"
GO

Of course, note that if you had any FKs/triggers disabled prior to this, the enable script will re-enable these.

score 0 · Answer 2 · answered Nov 01 '13 at 14:11

0

To disable all FKs in a table:

ALTER TABLE Table2 NOCHECK CONSTRAINT ALL

To disable a single FK in a table:

ALTER TABLE Table2 NOCHECK CONSTRAINT FK_Table2_Table1

To enable them replace NOCHECK with CHECK.

answered Nov 01 '13 at 14:11

GriGrim

2,891
1
19
33

score 0 · Answer 3 · answered Nov 01 '13 at 15:39

If you want to disable all FK's, and then restore them back to their original state, the following can be used:

Disable All Constraints

If OBJECT_ID('tempdb..#tempConstraints') is not null Drop Table #tempConstraints;
GO
IF (SELECT OBJECT_ID('tempdb..#tmpScriptErrors')) IS NOT NULL DROP TABLE #tmpScriptErrors
GO
CREATE TABLE #tmpScriptErrors (Error int)
GO
Create Table #tempConstraints
(
    ConstraintName nVarchar(200),
    TableName nVarchar(200),
    SchemaName nVarchar(200),
    IsNotTrusted bit
);
GO

Begin Tran

Insert into #tempConstraints (ConstraintName, TableName, SchemaName, IsNotTrusted)
Select K.name, object_name(K.parent_object_id), SCHEMA_NAME(T.schema_id), K.Is_Not_Trusted
FROM sys.foreign_keys K 
    Inner Join sys.tables T on K.parent_object_id = T.object_id
Where is_disabled = 0
Union all
Select K.name, object_name(K.parent_object_id), SCHEMA_NAME(T.schema_id), K.Is_Not_Trusted
from sys.check_constraints K
    Inner Join sys.tables T on K.parent_object_id = T.object_id
Where is_disabled = 0


--Disable the Constraints.
Print 'Disabling Constraints'
Exec sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';

Restore Constraints to Original State

Declare @name nvarchar(200);
Declare @table nvarchar(200);
Declare @schema nvarchar(200);
Declare @script nvarchar(max);
Declare @NotTrusted bit;

Declare constraints_cursor CURSOR FOR 
    Select ConstraintName, TableName, SchemaName, IsNotTrusted
    From #tempConstraints;

Open constraints_cursor;

Fetch Next from constraints_cursor
into @name, @table, @schema, @NotTrusted;

While @@FETCH_STATUS = 0
Begin
    --Restore each of the Constraints back to exactly the state they were in prior to disabling.

    If @NotTrusted = 1
        Set @script = 'ALTER TABLE [' + @schema + '].[' + @table + '] WITH NOCHECK CHECK CONSTRAINT [' + @name + ']';
    Else
        Set @script = 'ALTER TABLE [' + @schema + '].[' + @table + '] WITH CHECK CHECK CONSTRAINT [' + @name + ']';


    exec sp_executesql @script;
    If @@ERROR <> 0
    Begin
        PRINT 'Re-Enabling ' + @name;
        INSERT  INTO #tmpScriptErrors (Error)
        VALUES (1);
    End

    Fetch Next from constraints_cursor
    into @name, @table, @schema, @NotTrusted;
End
Close constraints_cursor;
Deallocate constraints_cursor;

If exists (Select 'x' from #tmpScriptErrors)
    ROLLBACK TRAN;
Else
    COMMIT TRAN;

Drop table #tmpScriptErrors
GO
Drop table #tempConstraints
GO

score -1 · Answer 4 · answered Nov 01 '13 at 13:46

-1

Disabling triggers in a single statement

DISABLE TRIGGER and ENABLE TRIGGER - check MSDN for their syntax. E.g., search google for : DISABLE TRIGGER t-sql

E.g., DISABLE TRIGGER ALL ON ALL SERVER; -- Disabling all DML triggers defined on server level and all logon triggers

There is no built-in way how to disable all FKs in a single statement.

answered Nov 01 '13 at 13:46

E.K.

419
2
3

1

Actually, there is. `EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'` – Obsidian Phoenix Nov 01 '13 at 15:40
Yes, you're right, but it depends on the definition of a single statement. sp_MSforeachtable is a procedure that has lots of statements inside it. However, I guess an SP call is good enough ... – E.K. Nov 18 '13 at 15:16

How to turn off/on all the foreign keys and triggers in Microsoft SQL Server?

4 Answers4