Turn off constraints temporarily (MS SQL)

Question

I'm looking for a way to temporarily turn off all DB's constraints (eg table relationships).

I need to copy (using INSERTs) one DB's tables to another DB. I know I can achieve that by executing commands in proper order (to not break relationships).

But it would be easier if I could turn off checking constraints temporarily and turn it back on after the operation's finish.

Is this possible?

This is not complete copy I just want to copy selected tables between — Maciej, Apr 10 '09 at 09:43
Dear people from The Future: You can disable and re-enable all constraints in the database at once; see http://stackoverflow.com/a/161410 — brichins, Feb 06 '13 at 00:39
@NicolasBarbulesco fair enough; I was going off the `sql-server` and `sql-server-2005` tags. The link I gave is for SQL Server, but you can do the same thing in Oracle - see [here](http://stackoverflow.com/questions/128623/disable-all-table-constraints-in-oracle) and [here](https://forums.oracle.com/thread/891801). You can also do it in [PostgreSQL](http://stackoverflow.com/questions/2679854/postgresql-disabling-constraints). — brichins, Oct 08 '13 at 22:56
My concern about doing this is that this turns off the constraints for everyone not just you. If you must do this, put the database in single user mode first. Otherwise you can end up data integrity problems. — HLGEM, Apr 10 '09 at 18:21

score 316 · Answer 1 · edited Sep 14 '21 at 06:52

316

-- Disable the constraints on a table called tableName:
ALTER TABLE tableName NOCHECK CONSTRAINT ALL

-- Re-enable the constraints on a table called tableName:
ALTER TABLE tableName WITH CHECK CHECK CONSTRAINT ALL
---------------------------------------------------------

-- Disable constraints for all tables in the database:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

-- Re-enable constraints for all tables in the database:
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
---------------------------------------------------------

edited Sep 14 '21 at 06:52

Serj Sagan

28,927
17
154
183

answered Oct 24 '14 at 16:30

Donal

31,121
10
63
72

1

@kevinc no. As long as you're consitent it doesn't matter. – Po-ta-toe Oct 07 '16 at 07:20
2

Using quoted identifiers is I believe the ANSI standard setting, which means you shouldn't use them for strings. Has nothing to do with being consistent. see http://stackoverflow.com/questions/1992314/what-is-the-difference-between-single-and-double-quotes-in-sql – kevinc Oct 08 '16 at 11:49
2

Thanks for the procedure! And BTW it's correct casing is "sp_MSforeachtable" (MS uppercase). Thanks! – Sielu Apr 28 '17 at 09:22
3

This should have been marked as the correct answer as it answers the question completely. Not only that, but @Donal included a wildcard version which was very helpful for me. – Matt Jackson Aug 31 '17 at 20:46
2

Thanks for the re-enabling bit. Especially the double `check check` which many folks forget!! – Alex from Jitbit Apr 18 '19 at 12:43

score 239 · Accepted Answer · edited Jun 22 '11 at 14:53

239

You can disable FK and CHECK constraints ~~only in SQL 2005+~~. See ALTER TABLE

ALTER TABLE foo NOCHECK CONSTRAINT ALL

or

ALTER TABLE foo NOCHECK CONSTRAINT CK_foo_column

Primary keys and unique constraints can not be disabled, but this should be OK if I've understood you correctly.

edited Jun 22 '11 at 14:53

Brian Gideon

47,849
13
107
150

answered Apr 10 '09 at 09:36

gbn

422,506
82
585
676

10

But this is not temporary. – Nicolas Barbulesco Oct 08 '13 at 12:30
@NicolasBarbulesco: it depends. Yes it is, in that you can re-enable them with DROP/CREATE – gbn Oct 09 '13 at 08:31
1

This answer is only the first half of a solution. I was looking for a simple way to turn off constraints temporarily, and I have reached the conclusion that it does not exist, on Oracle. – Nicolas Barbulesco Oct 10 '13 at 05:09
It is not true that PKs and unique constraint cannot be disabled. At least in more recent version of SQL Server it works. For example, see: http://www.techonthenet.com/sql_server/primary_keys.php – Dejan May 16 '15 at 12:23
1

@NicolasBarbulesco on Oracle? ALTER TABLE some_table DISABLE CONSTRAINT some_table_fk1; //do some stuff that would violate the constraints ALTER TABLE some_table ENABLE CONSTRAINT some_table_fk1; – Steve Swinsburg Jul 23 '15 at 05:29

score 57 · Answer 3 · edited Jun 22 '11 at 14:54

57

And, if you want to verify that you HAVEN'T broken your relationships and introduced orphans, once you have re-armed your checks, i.e.

ALTER TABLE foo CHECK CONSTRAINT ALL

or

ALTER TABLE foo CHECK CONSTRAINT FK_something

then you can run back in and do an update against any checked columns like so:

UPDATE myUpdatedTable SET someCol = someCol, fkCol = fkCol, etc = etc

And any errors at that point will be due to failure to meet constraints.

edited Jun 22 '11 at 14:54

Brian Gideon

47,849
13
107
150

answered Apr 10 '09 at 15:00

Michael K. Campbell

1,510
10
13

13

A better way is ALTER TABLE FOO WITH CHECK CHECK CONSTRAINT FK_something – Cody Konior Mar 27 '13 at 00:44
2

ALTER TABLE foo CHECK CONSTRAINT ALL or ALTER TABLE foo CHECK CONSTRAINT FK_something will enable constraints but without checking data and this means that constraint will be untrusted (is_no_trusted = 1, is_disabled = 0). – Bogdan Sahlean Jul 13 '17 at 10:41

score 18 · Answer 4 · edited May 23 '17 at 12:17

18

You can actually disable all database constraints in a single SQL command and the re-enable them calling another single command. See:

Can foreign key constraints be temporarily disabled using TSQL?

I am currently working with SQL Server 2005 but I am almost sure that this approach worked with SQL 2000 as well

edited May 23 '17 at 12:17

Community

1
1

answered Apr 21 '09 at 15:13

kristof

52,923
24
87
110

score 3 · Answer 5 · edited Sep 24 '14 at 15:21

Disabling and Enabling All Foreign Keys

CREATE PROCEDURE pr_Disable_Triggers_v2
    @disable BIT = 1
AS
    DECLARE @sql VARCHAR(500)
        ,   @tableName VARCHAR(128)
        ,   @tableSchema VARCHAR(128)

    -- List of all tables
    DECLARE triggerCursor CURSOR FOR
        SELECT  t.TABLE_NAME AS TableName
            ,   t.TABLE_SCHEMA AS TableSchema
        FROM    INFORMATION_SCHEMA.TABLES t
        ORDER BY t.TABLE_NAME, t.TABLE_SCHEMA

    OPEN    triggerCursor
    FETCH NEXT FROM triggerCursor INTO @tableName, @tableSchema
    WHILE ( @@FETCH_STATUS = 0 )
    BEGIN

        SET @sql = 'ALTER TABLE ' + @tableSchema + '.[' + @tableName + '] '
        IF @disable = 1
            SET @sql = @sql + ' DISABLE TRIGGER ALL'
        ELSE
            SET @sql = @sql + ' ENABLE TRIGGER ALL'

        PRINT 'Executing Statement - ' + @sql
        EXECUTE ( @sql )

        FETCH NEXT FROM triggerCursor INTO @tableName, @tableSchema

    END

    CLOSE triggerCursor
    DEALLOCATE triggerCursor

First, the foreignKeyCursor cursor is declared as the SELECT statement that gathers the list of foreign keys and their table names. Next, the cursor is opened and the initial FETCH statement is executed. This FETCH statement will read the first row's data into the local variables @foreignKeyName and @tableName. When looping through a cursor, you can check the @@FETCH_STATUS for a value of 0, which indicates that the fetch was successful. This means the loop will continue to move forward so it can get each successive foreign key from the rowset. @@FETCH_STATUS is available to all cursors on the connection. So if you are looping through multiple cursors, it is important to check the value of @@FETCH_STATUS in the statement immediately following the FETCH statement. @@FETCH_STATUS will reflect the status for the most recent FETCH operation on the connection. Valid values for @@FETCH_STATUS are:

0 = FETCH was successful
-1 = FETCH was unsuccessful
-2 = the row that was fetched is missing

Inside the loop, the code builds the ALTER TABLE command differently depending on whether the intention is to disable or enable the foreign key constraint (using the CHECK or NOCHECK keyword). The statement is then printed as a message so its progress can be observed and then the statement is executed. Finally, when all rows have been iterated through, the stored procedure closes and deallocates the cursor.

see Disabling Constraints and Triggers from MSDN Magazine

Turn off constraints temporarily (MS SQL)

5 Answers5

Linked

Related