Désactiver temporairement les contraintes (MS SQL)

Question

Je cherche un moyen de désactiver temporairement toutes les contraintes de la base de données (par exemple, les relations entre les tables).

Je dois copier (en utilisant des INSERTs) les tables d'une BD vers une autre BD. Je sais que je peux y parvenir en exécutant les commandes dans le bon ordre (pour ne pas rompre les relations).

Mais ce serait plus facile si je pouvais désactiver temporairement les contraintes de vérification et les réactiver une fois l'opération terminée.

Est-ce possible ?

Answer 1

Vous pouvez désactiver les contraintes FK et CHECK. uniquement dans SQL 2005+. . Voir ALTER TABLE

ALTER TABLE foo NOCHECK CONSTRAINT ALL

o

ALTER TABLE foo NOCHECK CONSTRAINT CK_foo_column

Les clés primaires et les contraintes uniques ne peuvent pas être désactivées, mais cela devrait être possible si j'ai bien compris.

Answer 2

Et, si vous voulez vérifier que vous n'avez pas brisé vos relations et introduit des orphelins, une fois que vous avez réarmé vos chèques, c'est à dire

ALTER TABLE foo CHECK CONSTRAINT ALL

o

ALTER TABLE foo CHECK CONSTRAINT FK_something

puis vous pouvez revenir en arrière et faire une mise à jour de toutes les colonnes cochées comme ceci :

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

Et toute erreur à ce stade sera due au non-respect des contraintes.

Answer 3

Vous pouvez en fait désactiver toutes les contraintes de base de données dans une seule commande SQL et les réactiver en appelant une autre commande unique. Voir :

• Les contraintes de clé étrangère peuvent-elles être temporairement désactivées à l'aide de TSQL ?

Je travaille actuellement avec SQL Server 2005, mais je suis presque sûr que cette approche fonctionne également avec SQL 2000.

Answer 4

Ce qui me préoccupe, c'est que cela désactive les contraintes pour tout le monde et pas seulement pour vous. Si vous devez le faire, mettez d'abord la base de données en mode mono-utilisateur. Sinon, vous risquez de rencontrer des problèmes d'intégrité des données.

Answer 5

Désactiver et activer toutes les clés étrangères

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

Tout d'abord, le curseur foreignKeyCursor est déclaré comme l'instruction SELECT qui rassemble la liste des clés étrangères et leurs noms de table. Ensuite, le curseur curseur est ouvert et l'instruction FETCH initiale est exécutée. Ce FETCH va lire les données de la première ligne dans les variables locales locales @foreignKeyName et @tableName. Lorsque vous effectuez une boucle dans un curseur, vous pouvez vérifier que la valeur de @@FETCH_STATUS est égale à 0, qui 0, ce qui indique que l'extraction a réussi. Cela signifie que la boucle va continuera d'avancer afin d'obtenir chaque clé étrangère successivement successive du jeu de lignes. @@FETCH_STATUS est disponible pour tous les curseurs de la connexion. Ainsi, si vous effectuez une boucle à travers plusieurs curseurs, il est important de vérifier la valeur de @@FETCH_STATUS dans l'instruction qui suit qui suit immédiatement l'instruction FETCH. @@FETCH_STATUS reflétera l'état de l'opération FETCH la plus récente sur la connexion. Les valeurs valides pour @@FETCH_STATUS sont :

0 = FETCH a réussi
-1 = FETCH a échoué.
-2 = la ligne qui a été récupérée est manquante.

À l'intérieur de la boucle, le code construit la commande ALTER TABLE différemment selon que l'intention est de désactiver ou d'activer la clé étrangère étrangère (en utilisant le mot-clé CHECK ou NOCHECK). L'instruction est ensuite imprimée sous forme de message afin de pouvoir observer sa progression, puis la boucle de l'instruction est exécutée. Enfin, lorsque toutes les lignes ont été parcourues par itération itérées, la procédure stockée ferme et désalloue le curseur.

voir Désactiver les contraintes et les déclencheurs de MSDN Magazine