Question Comment les contraintes de clé étrangère peuvent-elles être temporairement désactivées en utilisant T-SQL?


La désactivation et l'activation des contraintes de clé étrangère sont-elles prises en charge dans SQL Server? Ou est ma seule option pour drop et alors ré-create les contraintes?


743
2017-10-01 18:33


origine


Réponses:


Si vous voulez désactiver toutes les contraintes dans la base de données, exécutez simplement ce code:

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

Pour les rallumer, exécutez: (l'impression est facultative bien sûr et il s'agit juste d'énumérer les tableaux)

-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Je trouve cela utile lors du remplissage de données d'une base de données à une autre. C'est une bien meilleure approche que de faire tomber les contraintes. Comme vous l'avez mentionné, il est utile lorsque vous supprimez toutes les données de la base de données et que vous les repeuplerez (par exemple, dans un environnement de test).

Si vous supprimez toutes les données que vous pouvez trouver cette solution être utile.

Aussi parfois il est pratique de désactiver tous les déclencheurs, vous pouvez voir la solution complète ici.


1029
2017-10-02 08:50



http://www.sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx

-- Disable all table constraints

ALTER TABLE MyTable NOCHECK CONSTRAINT ALL

-- Enable all table constraints

ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT ALL

-- Disable single constraint

ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint

-- Enable single constraint

ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint

350
2017-10-01 18:37



Votre meilleure option consiste à DROP et CREER des contraintes de clé étrangère.

Je n'ai pas trouvé d'exemples dans ce post qui fonctionneraient pour moi "tel quel", on ne fonctionnerait pas si les clés étrangères référençaient des schémas différents, l'autre ne fonctionnerait pas si la clé étrangère référence plusieurs colonnes. Ce script considère à la fois plusieurs schémas et plusieurs colonnes par clé étrangère.

Voici le script qui génère les instructions "ADD CONSTRAINT", pour plusieurs colonnes, il les séparera par une virgule (assurez-vous de sauvegarder cette sortie avant d'exécuter les instructions DROP):

PRINT N'-- CREATE FOREIGN KEY CONSTRAINTS --';

SET NOCOUNT ON;
SELECT '
PRINT N''Creating '+ const.const_name +'...''
GO
ALTER TABLE ' + const.parent_obj + '
    ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY (
            ' + const.parent_col_csv + '
            ) REFERENCES ' + const.ref_obj + '(' + const.ref_col_csv + ')
GO'
FROM (
    SELECT QUOTENAME(fk.NAME) AS [const_name]
        ,QUOTENAME(schParent.NAME) + '.' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id))
                FROM sys.foreign_key_columns AS fcP
                WHERE fcp.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [parent_col_csv]
        ,QUOTENAME(schRef.NAME) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
                FROM sys.foreign_key_columns AS fcR
                WHERE fcR.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [ref_col_csv]
    FROM sys.foreign_key_columns AS fkc
    INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id
    INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id
    INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id
    GROUP BY fkc.parent_object_id
        ,fkc.referenced_object_id
        ,fk.NAME
        ,fk.object_id
        ,schParent.NAME
        ,schRef.NAME
    ) AS const
ORDER BY const.const_name

Voici le script qui génère les instructions "DROP CONSTRAINT":

PRINT N'-- DROP FOREIGN KEY CONSTRAINTS --';

SET NOCOUNT ON;

SELECT '
PRINT N''Dropping ' + fk.NAME + '...''
GO
ALTER TABLE [' + sch.NAME + '].[' + OBJECT_NAME(fk.parent_object_id) + ']' + ' DROP  CONSTRAINT ' + '[' + fk.NAME + ']
GO'
FROM sys.foreign_keys AS fk
INNER JOIN sys.schemas AS sch ON sch.schema_id = fk.schema_id
ORDER BY fk.NAME

24
2018-06-03 22:00



Pour désactiver la contrainte, vous avez ALTER la table en utilisant NOCHECK

ALTER TABLE [TABLE_NAME] NOCHECK CONSTRAINT [ALL|CONSTRAINT_NAME]

Pour vous permettre d'utiliser double VÉRIFIER

ALTER TABLE [TABLE_NAME] WITH CHECK CHECK CONSTRAINT [ALL|CONSTRAINT_NAME]
  • Faites attention au double CHECK CHECK lors de l'activation.
  • TOUT signifie pour toutes les contraintes de la table.

Une fois terminé, si vous avez besoin de vérifier l'état, utilisez ce script pour lister le statut de la contrainte. Sera très utile:

    SELECT (CASE 
        WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
        ELSE 'DISABLED'
        END) AS STATUS,
        OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
        OBJECT_NAME(FKEYID) AS TABLE_NAME,
        COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
        OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
        COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
   FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO 

21
2018-04-03 20:06



Le standard SQL-92 permet de déclarer une contrainte comme DEFERRABLE afin qu'elle puisse être différée (implicitement ou explicitement) dans le cadre d'une transaction. Malheureusement, SQL Server n'a toujours pas cette fonctionnalité SQL-92.

Pour moi, changer une contrainte en NOCHECK revient à changer la structure de la base de données à la volée - les contraintes de suppression sont certainement - et quelque chose à éviter (par exemple, les utilisateurs ont besoin de privilèges accrus).


9
2017-10-02 11:01



   --Drop and Recreate Foreign Key Constraints

SET NOCOUNT ON

DECLARE @table TABLE(
   RowId INT PRIMARY KEY IDENTITY(1, 1),
   ForeignKeyConstraintName NVARCHAR(200),
   ForeignKeyConstraintTableSchema NVARCHAR(200),
   ForeignKeyConstraintTableName NVARCHAR(200),
   ForeignKeyConstraintColumnName NVARCHAR(200),
   PrimaryKeyConstraintName NVARCHAR(200),
   PrimaryKeyConstraintTableSchema NVARCHAR(200),
   PrimaryKeyConstraintTableName NVARCHAR(200),
   PrimaryKeyConstraintColumnName NVARCHAR(200)    
)

INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT 
   U.CONSTRAINT_NAME, 
   U.TABLE_SCHEMA, 
   U.TABLE_NAME, 
   U.COLUMN_NAME 
FROM 
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
      INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
         ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
   C.CONSTRAINT_TYPE = 'FOREIGN KEY'

UPDATE @table SET
   PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM 
   @table T
      INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
         ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

UPDATE @table SET
   PrimaryKeyConstraintTableSchema  = TABLE_SCHEMA,
   PrimaryKeyConstraintTableName  = TABLE_NAME
FROM @table T
   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
      ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

UPDATE @table SET
   PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
      ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

--SELECT * FROM @table

--DROP CONSTRAINT:
SELECT
   '
   ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + '] 
   DROP CONSTRAINT ' + ForeignKeyConstraintName + '

   GO'
FROM
   @table

--ADD CONSTRAINT:
SELECT
   '
   ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + '] 
   ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ')

   GO'
FROM
   @table

GO

Je suis d'accord avec vous, Hamlin. Lorsque vous transférez des données à l'aide de SSIS ou lorsque vous souhaitez répliquer des données, il semble tout à fait nécessaire de désactiver ou de supprimer temporairement les contraintes de clé étrangère, puis de les réactiver ou de les recréer. Dans ces cas, l'intégrité référentielle n'est pas un problème, car elle est déjà gérée dans la base de données source. Par conséquent, vous pouvez être assuré à ce sujet.


9
2018-02-26 10:24



SET NOCOUNT ON

DECLARE @table TABLE(
   RowId INT PRIMARY KEY IDENTITY(1, 1),
   ForeignKeyConstraintName NVARCHAR(200),
   ForeignKeyConstraintTableSchema NVARCHAR(200),
   ForeignKeyConstraintTableName NVARCHAR(200),
   ForeignKeyConstraintColumnName NVARCHAR(200),
   PrimaryKeyConstraintName NVARCHAR(200),
   PrimaryKeyConstraintTableSchema NVARCHAR(200),
   PrimaryKeyConstraintTableName NVARCHAR(200),
   PrimaryKeyConstraintColumnName NVARCHAR(200),
   UpdateRule NVARCHAR(100),
   DeleteRule NVARCHAR(100)   
)

INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT 
   U.CONSTRAINT_NAME, 
   U.TABLE_SCHEMA, 
   U.TABLE_NAME, 
   U.COLUMN_NAME
FROM 
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
      INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
         ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
   C.CONSTRAINT_TYPE = 'FOREIGN KEY'

UPDATE @table SET
   T.PrimaryKeyConstraintName = R.UNIQUE_CONSTRAINT_NAME,
   T.UpdateRule = R.UPDATE_RULE,
   T.DeleteRule = R.DELETE_RULE
FROM 
   @table T
      INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
         ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

UPDATE @table SET
   PrimaryKeyConstraintTableSchema  = TABLE_SCHEMA,
   PrimaryKeyConstraintTableName  = TABLE_NAME
FROM @table T
   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
      ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

UPDATE @table SET
   PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
      ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

--SELECT * FROM @table

SELECT '
BEGIN TRANSACTION
BEGIN TRY'

--DROP CONSTRAINT:
SELECT
   '
 ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + '] 
 DROP CONSTRAINT ' + ForeignKeyConstraintName + '
   '
FROM
   @table

SELECT '
END TRY

BEGIN CATCH
   ROLLBACK TRANSACTION
   RAISERROR(''Operation failed.'', 16, 1)
END CATCH

IF(@@TRANCOUNT != 0)
BEGIN
   COMMIT TRANSACTION
   RAISERROR(''Operation completed successfully.'', 10, 1)
END
'

--ADD CONSTRAINT:
SELECT '
BEGIN TRANSACTION
BEGIN TRY'

SELECT
   '
   ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + '] 
   ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ') ON UPDATE ' + UpdateRule + ' ON DELETE ' + DeleteRule + '
   '
FROM
   @table

SELECT '
END TRY

BEGIN CATCH
   ROLLBACK TRANSACTION
   RAISERROR(''Operation failed.'', 16, 1)
END CATCH

IF(@@TRANCOUNT != 0)
BEGIN
   COMMIT TRANSACTION
   RAISERROR(''Operation completed successfully.'', 10, 1)
END'

GO

9
2018-06-19 19:13



Premier poste :)

Pour l'OP, la solution de kristof fonctionnera, à moins qu'il y ait des problèmes avec des problèmes massifs de ballons de données et de journaux de transactions lors de grosses suppressions. En outre, même avec le stockage tlog à épargner, puisque les suppressions sont écrites sur le tlog, l'opération peut prendre TRES longtemps pour les tables contenant des centaines de millions de lignes.

J'utilise une série de curseurs pour tronquer et recharger fréquemment de grandes copies de l'une de nos énormes bases de données de production. La solution conçue compte pour plusieurs schémas, plusieurs colonnes de clé étrangère, et le meilleur de tous peut être sproc'd dehors pour l'usage dans SSIS.

Il implique la création de trois tables de transfert (tables réelles) pour héberger les scripts DROP, CREATE et CHECK FK, la création et l'insertion de ces scripts dans les tables, puis la mise en boucle des tables et leur exécution. Le script joint est constitué de quatre parties: 1.) création et stockage des scripts dans les trois tables de transfert (réelles), 2.) exécution des scripts FK de dépôt via un curseur un par un, 3.) utilisation de sp_MSforeachtable pour tronquer tous les 4. Exécuter les scripts FK et vérifier les scripts FK à la fin de votre package ETL SSIS.

Exécutez la partie de création de script dans une tâche d'exécution SQL dans SSIS. Exécutez la partie "Exécuter Drop FK Scripts" dans une deuxième tâche d'exécution SQL. Placez le script de troncature dans une troisième tâche SQL d'exécution, puis effectuez les autres traitements ETL nécessaires avant d'attacher les scripts CREATE et CHECK dans une tâche finale d'exécution SQL (ou deux si vous le souhaitez) à la fin de votre flux de contrôle.

Le stockage des scripts dans les tables réelles s'est avéré inestimable lorsque la nouvelle application des clés étrangères échoue car vous pouvez sélectionner * depuis sync_CreateFK, copier / coller dans votre fenêtre de requête, les exécuter un à la fois, et résoudre les problèmes de données une fois trouver ceux qui ont échoué / échouent toujours à réappliquer.

Ne relancez pas le script s'il échoue sans vous assurer que vous réappliquez toutes les clés / vérifications étrangères avant de le faire, sinon vous perdrez probablement des créations et vérifierez les scripts fk lorsque vos tables de transfert seront supprimées. recréé avant la création des scripts à exécuter.

----------------------------------------------------------------------------
1)
/*
Author:         Denmach
DateCreated:    2014-04-23
Purpose:        Generates SQL statements to DROP, ADD, and CHECK existing constraints for a 
                database.  Stores scripts in tables on target database for execution.  Executes
                those stored scripts via independent cursors. 
DateModified:
ModifiedBy
Comments:       This will eliminate deletes and the T-log ballooning associated with it.
*/

DECLARE @schema_name SYSNAME; 
DECLARE @table_name SYSNAME; 
DECLARE @constraint_name SYSNAME; 
DECLARE @constraint_object_id INT; 
DECLARE @referenced_object_name SYSNAME; 
DECLARE @is_disabled BIT; 
DECLARE @is_not_for_replication BIT; 
DECLARE @is_not_trusted BIT; 
DECLARE @delete_referential_action TINYINT; 
DECLARE @update_referential_action TINYINT; 
DECLARE @tsql NVARCHAR(4000); 
DECLARE @tsql2 NVARCHAR(4000); 
DECLARE @fkCol SYSNAME; 
DECLARE @pkCol SYSNAME; 
DECLARE @col1 BIT; 
DECLARE @action CHAR(6);  
DECLARE @referenced_schema_name SYSNAME;



--------------------------------Generate scripts to drop all foreign keys in a database --------------------------------

IF OBJECT_ID('dbo.sync_dropFK') IS NOT NULL
DROP TABLE sync_dropFK

CREATE TABLE sync_dropFK
    (
    ID INT IDENTITY (1,1) NOT NULL
    , Script NVARCHAR(4000)
    )

DECLARE FKcursor CURSOR FOR

    SELECT 
        OBJECT_SCHEMA_NAME(parent_object_id)
        , OBJECT_NAME(parent_object_id)
        , name
    FROM 
        sys.foreign_keys WITH (NOLOCK)
    ORDER BY 
        1,2;

OPEN FKcursor;

FETCH NEXT FROM FKcursor INTO 
    @schema_name
    , @table_name
    , @constraint_name

WHILE @@FETCH_STATUS = 0

BEGIN
        SET @tsql = 'ALTER TABLE '
                + QUOTENAME(@schema_name) 
                + '.' 
                + QUOTENAME(@table_name)
                + ' DROP CONSTRAINT ' 
                + QUOTENAME(@constraint_name) 
                + ';';
    --PRINT @tsql;
    INSERT sync_dropFK  (
                        Script
                        )
                        VALUES (
                                @tsql
                                )   

    FETCH NEXT FROM FKcursor INTO 
    @schema_name
    , @table_name
    , @constraint_name
    ;

END;

CLOSE FKcursor;

DEALLOCATE FKcursor;


---------------Generate scripts to create all existing foreign keys in a database --------------------------------
----------------------------------------------------------------------------------------------------------
IF OBJECT_ID('dbo.sync_createFK') IS NOT NULL
DROP TABLE sync_createFK

CREATE TABLE sync_createFK
    (
    ID INT IDENTITY (1,1) NOT NULL
    , Script NVARCHAR(4000)
    )

IF OBJECT_ID('dbo.sync_createCHECK') IS NOT NULL
DROP TABLE sync_createCHECK

CREATE TABLE sync_createCHECK
    (
    ID INT IDENTITY (1,1) NOT NULL
    , Script NVARCHAR(4000)
    )   

DECLARE FKcursor CURSOR FOR

     SELECT 
        OBJECT_SCHEMA_NAME(parent_object_id)
        , OBJECT_NAME(parent_object_id)
        , name
        , OBJECT_NAME(referenced_object_id)
        , OBJECT_ID
        , is_disabled
        , is_not_for_replication
        , is_not_trusted
        , delete_referential_action
        , update_referential_action
        , OBJECT_SCHEMA_NAME(referenced_object_id)

    FROM 
        sys.foreign_keys WITH (NOLOCK)

    ORDER BY 
        1,2;

OPEN FKcursor;

FETCH NEXT FROM FKcursor INTO 
    @schema_name
    , @table_name
    , @constraint_name
    , @referenced_object_name
    , @constraint_object_id
    , @is_disabled
    , @is_not_for_replication
    , @is_not_trusted
    , @delete_referential_action
    , @update_referential_action
    , @referenced_schema_name;

WHILE @@FETCH_STATUS = 0

BEGIN

        BEGIN
            SET @tsql = 'ALTER TABLE '
                        + QUOTENAME(@schema_name) 
                        + '.' 
                        + QUOTENAME(@table_name)
                        +   CASE 
                                @is_not_trusted
                                WHEN 0 THEN ' WITH CHECK '
                                ELSE ' WITH NOCHECK '
                            END
                        + ' ADD CONSTRAINT ' 
                        + QUOTENAME(@constraint_name)
                        + ' FOREIGN KEY (';

        SET @tsql2 = '';

        DECLARE ColumnCursor CURSOR FOR 

            SELECT 
                COL_NAME(fk.parent_object_id
                , fkc.parent_column_id)
                , COL_NAME(fk.referenced_object_id
                , fkc.referenced_column_id)

            FROM 
                sys.foreign_keys fk WITH (NOLOCK)
                INNER JOIN sys.foreign_key_columns fkc WITH (NOLOCK) ON fk.[object_id] = fkc.constraint_object_id

            WHERE 
                fkc.constraint_object_id = @constraint_object_id

            ORDER BY 
                fkc.constraint_column_id;

        OPEN ColumnCursor;

        SET @col1 = 1;

        FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;

        WHILE @@FETCH_STATUS = 0

        BEGIN
            IF (@col1 = 1)
                SET @col1 = 0;
            ELSE
            BEGIN
                SET @tsql = @tsql + ',';
                SET @tsql2 = @tsql2 + ',';
            END;

            SET @tsql = @tsql + QUOTENAME(@fkCol);
            SET @tsql2 = @tsql2 + QUOTENAME(@pkCol);
            --PRINT '@tsql = ' + @tsql 
            --PRINT '@tsql2 = ' + @tsql2
            FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;
            --PRINT 'FK Column ' + @fkCol
            --PRINT 'PK Column ' + @pkCol 
        END;

        CLOSE ColumnCursor;
        DEALLOCATE ColumnCursor;

        SET @tsql = @tsql + ' ) REFERENCES ' 
                    + QUOTENAME(@referenced_schema_name) 
                    + '.' 
                    + QUOTENAME(@referenced_object_name)
                    + ' (' 
                    + @tsql2 + ')';

        SET @tsql = @tsql
                    + ' ON UPDATE ' 
                    + 
                        CASE @update_referential_action
                            WHEN 0 THEN 'NO ACTION '
                            WHEN 1 THEN 'CASCADE '
                            WHEN 2 THEN 'SET NULL '
                                ELSE 'SET DEFAULT '
                        END

                    + ' ON DELETE ' 
                    + 
                        CASE @delete_referential_action
                            WHEN 0 THEN 'NO ACTION '
                            WHEN 1 THEN 'CASCADE '
                            WHEN 2 THEN 'SET NULL '
                                ELSE 'SET DEFAULT '
                        END

                    + 
                    CASE @is_not_for_replication
                        WHEN 1 THEN ' NOT FOR REPLICATION '
                            ELSE ''
                    END
                    + ';';

        END;

    --  PRINT @tsql
        INSERT sync_createFK    
                        (
                        Script
                        )
                        VALUES (
                                @tsql
                                )

-------------------Generate CHECK CONSTRAINT scripts for a database ------------------------------
----------------------------------------------------------------------------------------------------------

        BEGIN

        SET @tsql = 'ALTER TABLE '
                    + QUOTENAME(@schema_name) 
                    + '.' 
                    + QUOTENAME(@table_name)
                    + 
                        CASE @is_disabled
                            WHEN 0 THEN ' CHECK '
                                ELSE ' NOCHECK '
                        END
                    + 'CONSTRAINT ' 
                    + QUOTENAME(@constraint_name)
                    + ';';
        --PRINT @tsql;
        INSERT sync_createCHECK 
                        (
                        Script
                        )
                        VALUES (
                                @tsql
                                )   
        END;

    FETCH NEXT FROM FKcursor INTO 
    @schema_name
    , @table_name
    , @constraint_name
    , @referenced_object_name
    , @constraint_object_id
    , @is_disabled
    , @is_not_for_replication
    , @is_not_trusted
    , @delete_referential_action
    , @update_referential_action
    , @referenced_schema_name;

END;

CLOSE FKcursor;

DEALLOCATE FKcursor;

--SELECT * FROM sync_DropFK
--SELECT * FROM sync_CreateFK
--SELECT * FROM sync_CreateCHECK
---------------------------------------------------------------------------
2.)
-----------------------------------------------------------------------------------------------------------------
----------------------------execute Drop FK Scripts --------------------------------------------------

DECLARE @scriptD NVARCHAR(4000)

DECLARE DropFKCursor CURSOR FOR
    SELECT Script 
    FROM sync_dropFK WITH (NOLOCK)

OPEN DropFKCursor

FETCH NEXT FROM DropFKCursor
INTO @scriptD

WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @scriptD
EXEC (@scriptD)
FETCH NEXT FROM DropFKCursor
INTO @scriptD
END
CLOSE DropFKCursor
DEALLOCATE DropFKCursor
--------------------------------------------------------------------------------
3.) 

------------------------------------------------------------------------------------------------------------------
----------------------------Truncate all tables in the database other than our staging tables --------------------
------------------------------------------------------------------------------------------------------------------


EXEC sp_MSforeachtable 'IF OBJECT_ID(''?'') NOT IN 
(
ISNULL(OBJECT_ID(''dbo.sync_createCHECK''),0),
ISNULL(OBJECT_ID(''dbo.sync_createFK''),0),
ISNULL(OBJECT_ID(''dbo.sync_dropFK''),0)
)
BEGIN TRY
 TRUNCATE TABLE ?
END TRY
BEGIN CATCH
 PRINT ''Truncation failed on''+ ? +''
END CATCH;' 
GO
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
----------------------------execute Create FK Scripts and CHECK CONSTRAINT Scripts---------------
----------------------------tack me at the end of the ETL in a SQL task-------------------------
-------------------------------------------------------------------------------------------------
DECLARE @scriptC NVARCHAR(4000)

DECLARE CreateFKCursor CURSOR FOR
    SELECT Script 
    FROM sync_createFK WITH (NOLOCK)

OPEN CreateFKCursor

FETCH NEXT FROM CreateFKCursor
INTO @scriptC

WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @scriptC
EXEC (@scriptC)
FETCH NEXT FROM CreateFKCursor
INTO @scriptC
END
CLOSE CreateFKCursor
DEALLOCATE CreateFKCursor
-------------------------------------------------------------------------------------------------
DECLARE @scriptCh NVARCHAR(4000)

DECLARE CreateCHECKCursor CURSOR FOR
    SELECT Script 
    FROM sync_createCHECK WITH (NOLOCK)

OPEN CreateCHECKCursor

FETCH NEXT FROM CreateCHECKCursor
INTO @scriptCh

WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @scriptCh
EXEC (@scriptCh)
FETCH NEXT FROM CreateCHECKCursor
INTO @scriptCh
END
CLOSE CreateCHECKCursor
DEALLOCATE CreateCHECKCursor

7
2018-02-16 08:16



WITH CHECK CHECK est presque certainement nécessaire!

Ce point a été soulevé dans certaines réponses et commentaires, mais je pense que c'est assez important pour l'appeler de nouveau.

Réactivation d'une contrainte à l'aide de la commande suivante (non WITH CHECK) aura quelques sérieux inconvénients.

ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint;

AVEC CHECK | AVEC NOCHECK

Indique si les données de la table sont ou non validées par rapport à   une contrainte FOREIGN KEY ou CHECK nouvellement ajoutée ou ré-activée. Si non   spécifié, WITH CHECK est supposé pour les nouvelles contraintes et WITH NOCHECK   est supposé pour les contraintes réactivées.

Si vous ne souhaitez pas vérifier les nouvelles contraintes CHECK ou FOREIGN KEY   par rapport aux données existantes, utilisez WITH NOCHECK. Nous ne recommandons pas   ceci, sauf dans de rares cas. La nouvelle contrainte sera évaluée dans   toutes les mises à jour de données ultérieures. Toute violation de contrainte supprimée   par WITH NOCHECK lorsque la contrainte est ajoutée peut entraîner des mises à jour futures   échouer si elles mettent à jour des lignes avec des données qui ne sont pas conformes à la   contrainte.

L'optimiseur de requête ne prend pas en compte les contraintes définies   AVEC NOCHECK. Ces contraintes sont ignorées jusqu'à ce qu'elles soient réactivées   en utilisant la table ALTER TABLE AVEC CHECK CHECK CONSTRAINT ALL.

Remarque: WITH NOCHECK est la valeur par défaut pour la réactivation des contraintes. Je dois me demander pourquoi ...

  1. Aucune donnée existante dans la table ne sera évaluée pendant l'exécution de cette commande - l'achèvement réussi n'est aucune garantie que les données dans la table sont valides selon la contrainte.
  2. Lors de la prochaine mise à jour des enregistrements non valides, la contrainte sera évaluée et échouera, ce qui entraînera des erreurs sans rapport avec la mise à jour effectuée.
  3. La logique d'application qui repose sur la contrainte pour garantir que les données sont valides peut échouer.
  4. L'optimiseur de requête n'utilisera aucune contrainte activée de cette manière.

le sys.foreign_keys La vue système offre une certaine visibilité sur le problème. Notez qu'il a à la fois un is_disabled Et un is_not_trusted colonne. is_disabled indique si les futures opérations de manipulation de données seront validées par rapport à la contrainte. is_not_trusted indique si toutes les données actuellement dans la table ont été validées par rapport à la contrainte.

ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint;

Vos contraintes sont-elles fiables? Trouver...

SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1;

6
2018-02-14 06:31



Trouver la contrainte

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('TABLE_NAME')

Exécute le SQL généré par ce SQL

SELECT 
    'ALTER TABLE ' +  OBJECT_SCHEMA_NAME(parent_object_id) +
    '.[' + OBJECT_NAME(parent_object_id) + 
    '] DROP CONSTRAINT ' + name
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('TABLE_NAME')

Safeway.

Remarque: Ajout de la solution pour supprimer la contrainte afin que la table puisse être supprimée ou modifiée sans aucune erreur de contrainte.


5
2018-05-16 09:49