Временно отключить ограничения (MS SQL)

Я ищу способ временно отключить все ограничения БД (например, отношения таблиц).

Мне нужно скопировать (используя вставки) таблицы одной БД в другую БД. Я знаю, что могу добиться этого, выполняя команды в правильном порядке (чтобы не нарушать отношения).

но было бы проще, если бы я мог временно отключить проверку ограничений и снова включить его после завершения операции.

это возможно?

5 183
sql sql-server entity-relationship sql-server-2005 constraints

5 ответов:

вы можете отключить FK и проверить ограничения только в SQL 2005+. Смотрите ALTER TABLE

ALTER TABLE foo NOCHECK CONSTRAINT ALL

или

ALTER TABLE foo NOCHECK CONSTRAINT CK_foo_column

первичные ключи и уникальные ограничения не могут быть отключены, но это должно быть нормально, если я правильно вас понял.

200
-- 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:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

-- Re-enable constraints for all tables:
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'
---------------------------------------------------------
166

и, если вы хотите проверить, что вы не нарушили свои отношения и не ввели сирот, как только вы перевооружили свои чеки, т. е.

ALTER TABLE foo CHECK CONSTRAINT ALL

или

ALTER TABLE foo CHECK CONSTRAINT FK_something

затем вы можете запустить обратно и сделать обновление для любых проверенных столбцов следующим образом:

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

и любые ошибки в этот момент будут связаны с невыполнением ограничений.

55

вы можете фактически отключить все ограничения базы данных в одной команде SQL и повторно включить их вызов другой команды. Смотрите:

в настоящее время я работаю с SQL Server 2005, но я почти уверен, что этот подход работал и с SQL 2000

16

отключение и включение всех внешних ключей

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

во-первых, курсор foreignKeyCursor объявляется как оператор SELECT это собирает список внешних ключей и их имена таблиц. Далее открывается курсор и выполняется начальная инструкция FETCH. Этот Оператор FETCH будет считывать данные первой строки в локальную переменные @foreignKeyName и @tableName. После перехода через курсор, вы можете проверить @@FETCH_STATUS для a значение 0, что указывает, что выборка прошла успешно. Это означает, что цикл будет продолжайте двигаться вперед, чтобы он мог получить каждый последующий внешний ключ из набора строк. @@FETCH_STATUS доступен для всех курсоров на соединение. Поэтому, если вы зацикливаетесь на нескольких курсорах, это важно проверить значение @@FETCH_STATUS в инструкции сразу после получения заявления. @Функция @ @ fetch_status будет отражать состояние самой последней операции выборки на устройстве соединение. Допустимые значения для @@FETCH_STATUS:

0 = выборка прошла успешно
-1 = выборка не удалась
-2 = строка, которая была получена отсутствует

внутри цикла код строит команду ALTER TABLE по-разному в зависимости от того, является ли намерение отключить или включить иностранный ограничение ключа (с помощью ключевого слова CHECK или NOCHECK). Заявление затем печатается как сообщение, чтобы его прогресс можно было наблюдать, а затем инструкция выполняется. Наконец, когда все строки были повторены после этого хранимая процедура закрывается и освобождает курсор.

посмотреть отключение ограничений и триггеров из журнала MSDN

2