Tymczasowo wyłącz ograniczenia (MS SQL)


208

Szukam sposobu na tymczasowe wyłączenie wszystkich ograniczeń DB (np. Relacji między tabelami).

Muszę skopiować (używając INSERT) tabele jednego DB do innego DB. Wiem, że mogę to osiągnąć, wykonując polecenia w odpowiedniej kolejności (aby nie zerwać relacji).

Byłoby jednak łatwiej, gdybym mógł tymczasowo wyłączyć sprawdzanie ograniczeń i włączyć go ponownie po zakończeniu operacji.

czy to możliwe?


3
To nie jest kompletna kopia Chcę tylko skopiować wybrane tabele między
Maciej

Martwię się tym, że to wyłącza ograniczenia dla wszystkich, nie tylko dla ciebie. Jeśli musisz to zrobić, ustaw najpierw bazę danych w trybie pojedynczego użytkownika. W przeciwnym razie mogą wystąpić problemy z integralnością danych.
HLGEM

13
Drodzy ludzie z Przyszłości: Możesz jednocześnie wyłączyć i ponownie włączyć wszystkie ograniczenia w bazie danych; patrz stackoverflow.com/a/161410
brichins

1
Po zakończeniu nie zapomnij włączyć ograniczeń!
Mike Christian,

1
@NicolasBarbulesco wystarczy; Zaczynałem od tagów sql-serveri sql-server-2005. Link, który podałem, dotyczy programu SQL Server, ale możesz zrobić to samo w Oracle - patrz tutaj i tutaj . Możesz to również zrobić w PostgreSQL .
brichins

Odpowiedzi:


214

Możesz wyłączyć ograniczenia FK i CHECK tylko w SQL 2005+ . Zobacz ZMIENIĆ TABELĘ

ALTER TABLE foo NOCHECK CONSTRAINT ALL

lub

ALTER TABLE foo NOCHECK CONSTRAINT CK_foo_column

Klucze podstawowe i unikalne ograniczenia nie mogą być wyłączone, ale powinno być OK, jeśli dobrze cię zrozumiałem.


10
Ale to nie jest tymczasowe.
Nicolas Barbulesco

@NicolasBarbulesco: to zależy. Tak, możesz włączyć je ponownie za pomocą DROP / CREATE
gbn

Ta odpowiedź to tylko pierwsza połowa rozwiązania. Szukałem prostego sposobu tymczasowego wyłączenia ograniczeń i doszedłem do wniosku, że na Oracle nie istnieje.
Nicolas Barbulesco

Nie jest prawdą, że PK i unikalne ograniczenia nie mogą być wyłączone. Przynajmniej w najnowszej wersji SQL Server działa. Na przykład patrz: techonthenet.com/sql_server/primary_keys.php
Dejan

1
@NicolasBarbulesco na Oracle? ALTER TABLE some_table DISABLE CONSTRAINT some_table_fk1; // wykonaj pewne czynności, które naruszałyby ograniczenia ALTER TABLE some_table ENABLE CONSTRAINT some_table_fk1;
Steve Swinsburg

237
-- 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'
---------------------------------------------------------

1
@kevinc no. Dopóki jesteś konsekwentny, nie ma to znaczenia.
Po-ta-toe

2
Używając cytowanych identyfikatorów uważam standardowe ustawienie ANSI, co oznacza, że ​​nie powinieneś ich używać do łańcuchów. Nie ma to nic wspólnego z konsekwencją. patrz stackoverflow.com/questions/1992314/…
kevinc

1
Dzięki za procedurę! A tak przy okazji, jego poprawna obudowa to „sp_MSforeachtable” (MS wielkie litery). Dzięki!
Sielu

3
Powinno to zostać oznaczone jako poprawna odpowiedź, ponieważ całkowicie odpowiada na pytanie. Poza tym @Donal zawierał wersję wieloznaczną, która była dla mnie bardzo pomocna.
Matt Jackson

2
Dzięki za bit ponownie włączający. Zwłaszcza podwójne, o check checkktórym zapomina wielu ludzi !!
Alex

57

A jeśli chcesz sprawdzić, czy NIE ZŁOŻYŁEŚ swoich związków i nie wprowadziłeś sierot, po ponownym uzbrojeniu czeków, tj.

ALTER TABLE foo CHECK CONSTRAINT ALL

lub

ALTER TABLE foo CHECK CONSTRAINT FK_something

możesz ponownie uruchomić się i wykonać aktualizację w przypadku zaznaczonych kolumn, takich jak:

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

A wszelkie błędy w tym punkcie będą spowodowane brakiem spełnienia ograniczeń.


11
Lepszym sposobem jest ALTER TABLE FOO z kontrolą ograniczenia CHECK FK_something
Cody Konior

1
ALTER TABLE foo CHECK CONSTRAINT ALL lub ALTER TABLE foo CHECK CONSTRAINT FK_something włącza ograniczenia, ale bez sprawdzania danych, a to oznacza, że ​​ograniczenie będzie niezaufane (is_no_trusted = 1, is_disabled = 0).
Bogdan Sahlean,


0

Wyłączanie i włączanie wszystkich kluczy obcych

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

Najpierw kursor ForeignKeyCursor jest zadeklarowany jako instrukcja SELECT, która gromadzi listę kluczy obcych i ich nazwy tabel. Następnie kursor jest otwierany i wykonywana jest początkowa instrukcja FETCH. Ta instrukcja FETCH wczyta dane pierwszego wiersza do zmiennych lokalnych @foreignKeyName i @tableName. Podczas zapętlania kursora możesz sprawdzić @@ FETCH_STATUS pod kątem wartości 0, co oznacza, że ​​pobieranie się powiodło. Oznacza to, że pętla będzie kontynuowała ruch do przodu, dzięki czemu będzie mogła pobrać każdy kolejny klucz obcy z zestawu wierszy. @@ FETCH_STATUS jest dostępny dla wszystkich kursorów w połączeniu. Jeśli więc zapętlasz wiele kursorów, ważne jest, aby sprawdzić wartość @@ FETCH_STATUS w instrukcji bezpośrednio po instrukcji FETCH. @@ FETCH_STATUS będzie odzwierciedlać stan ostatniej operacji FETCH na połączeniu. Prawidłowe wartości dla @@ FETCH_STATUS to:

0 = FETCH zakończył się sukcesem
-1 = FETCH nie powiódł się
-2 = brakuje pobranego wiersza

Wewnątrz pętli kod buduje komendę ALTER TABLE w różny sposób, zależnie od tego, czy intencją jest wyłączenie, czy włączenie ograniczenia klucza obcego (za pomocą słowa kluczowego CHECK lub NOCHECK). Instrukcja jest następnie drukowana jako komunikat, dzięki czemu można obserwować jej postęp, a następnie instrukcja jest wykonywana. Wreszcie, po przejściu wszystkich wierszy procedura przechowywana zamyka się i zwalnia kursor.

zobacz Wyłączanie ograniczeń i wyzwalaczy z MSDN Magazine

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.