SQL Server & Delphi

Non avete mai avuto la necessità di disabilitare momentaneamente i vincoli FOREIGNKEYS su una o più tabelle ?

Ho avuto questa necessità durante la conversione di tipi dato per le colonne di alcune tabelle di un DB. Non conoscevo a priori il numero e il nome dei constraint e non vi era una regola universale utilizzata per la nomenclatura di questi vincoli.

Dopo un po' di tempo investito a studiare le tabelle di sistema... è arrivata una stored procedure CS_SP_ENABLE_DISABLE_FK_CONSTRAINT_ON_TABLE() che permette di abilitare/disabilitare i vincoli FOREIGNKEYS e l'assegnazione di valori nei campi definiti IDENTITY() nelle tabelle passate come parametro.

Compatibilità con SQL Server 2000.

IF OBJECT_ID('CS_SP_ENABLE_DISABLE_FK_CONSTRAINT_ON_TABLE', 'P') IS NOT NULL
  DROP PROCEDURE DBO.CS_SP_ENABLE_DISABLE_FK_CONSTRAINT_ON_TABLE
GO

CREATE PROCEDURE CS_SP_ENABLE_DISABLE_FK_CONSTRAINT_ON_TABLE
  (@TABLE_LIST VARCHAR(8000),
   @ENABLE SMALLINT,
   @ALLOWS_IDENTITY_INSERT SMALLINT)

AS BEGIN
 
  /*
     Descrizione   : Permette di abilitare/disabilitare i CONSTRAINT di tipo FOREIGN KEYS definiti sulle tabelle

                           contenute nella lista @TABLE_LIST

     Parametri     : @TABLE_LIST = String lista separata da virgole "," in cui elencare le tabelle interessate

                          @ENABLE = Smallint per indicare alla stored procedure se
                                              si desidera disabilitare o abilitare le FOREIGN KEYS.
                                              I valori riconosciuti sono 0 per disabilitare e -1 per
                                              abilitare le FOREIGN KEYS. Non ha default, se viene
                                              specificato un valore diverso da quelli previsti si
                                              assume by default il valore -1 (= abilitazione)

                          @ALLOWS_IDENTITY_INSERT = Smallint per indicare alla stored procedure se 
                                                                            si desidera permettere l'assegnazione di valori
                                                                            sui campi definiti IDENTITY.
                                                                            I valori riconosciuti sono 0 per NON consentire e -1 per
                                                                            consentire l'assegnazione di valori sui campi definiti
                                                                            IDENTITY. Non ha default, se viene
                                                                            specificato un valore diverso da quelli previsti si
                                                                            assume by default il valore 0 (= NON consentire)

  */

  --SET NOCOUNT ON

  DECLARE @STR_COMMAND AS VARCHAR(512),
          @STR_COMMAND_SET_IDENTITY AS VARCHAR(512),
          @STR_TABLE_NAME AS VARCHAR(128),
          @STR_TMP VARCHAR(8000)

  SET @STR_TMP = ''
 
  DECLARE CUR_DISABLE_CONSTRAINT CURSOR FOR
   
    SELECT LTRIM(RTRIM(CAST(SYSOBJECTS.NAME AS VARCHAR(128)))),

           ('SET IDENTITY_INSERT ' +
            LTRIM(RTRIM(CAST(SYSOBJECTS.NAME AS VARCHAR))) +
            CASE (@ALLOWS_IDENTITY_INSERT)
              WHEN (-1) THEN ' ON '
              WHEN (0) THEN ' OFF '
              ELSE ' OFF '
            END
           ) CMD_IDENTITY_INSERT,

           ('ALTER TABLE ' +
            LTRIM(RTRIM(CAST(SYSOBJECTS.NAME AS VARCHAR))) +
            CASE (@ENABLE)
              WHEN (-1) THEN ' WITH CHECK CHECK CONSTRAINT ' 
              WHEN (0) THEN ' NOCHECK CONSTRAINT '
              ELSE ' WITH CHECK CHECK CONSTRAINT '
            END +
            (SELECT OBJ_FK.NAME
             FROM SYSOBJECTS OBJ_FK
             WHERE OBJ_FK.ID=SYSCONSTRAINTS.CONSTID)
           ) CMD_DISABLE_CONSTRAINT
   
    FROM SYSCONSTRAINTS
    INNER JOIN SYSOBJECTS ON SYSOBJECTS.ID=SYSCONSTRAINTS.ID
    INNER JOIN SYSFOREIGNKEYS ON SYSFOREIGNKEYS.CONSTID=SYSCONSTRAINTS.CONSTID
    WHERE CHARINDEX((',' + LTRIM(RTRIM(UPPER(SYSOBJECTS.NAME))) + ','), -- Table (Es: 'TestTabA, TestTabB, TestTabG')
                    (',' + REPLACE(@TABLE_LIST, ' ', '') + ',')) > 0
    ORDER BY SYSOBJECTS.NAME,
             (SELECT OBJ_FK.NAME
              FROM SYSOBJECTS OBJ_FK
              WHERE OBJ_FK.ID=SYSCONSTRAINTS.CONSTID)

  OPEN CUR_DISABLE_CONSTRAINT

  FETCH NEXT FROM CUR_DISABLE_CONSTRAINT INTO @STR_TABLE_NAME, @STR_COMMAND_SET_IDENTITY, @STR_COMMAND
 
  WHILE (@@FETCH_STATUS = 0)
  BEGIN
    IF (@STR_COMMAND <> '')
    BEGIN
      PRINT @STR_COMMAND
      EXECUTE (@STR_COMMAND)
    END

    IF (@STR_COMMAND_SET_IDENTITY <> '') AND
       (CHARINDEX(@STR_TABLE_NAME, @STR_TMP) = 0)
    BEGIN
      PRINT @STR_COMMAND_SET_IDENTITY
      EXECUTE (@STR_COMMAND_SET_IDENTITY)
      SET @STR_TMP = @STR_TMP + ' ' + @STR_TABLE_NAME
    END
    ELSE
      PRINT '/* ' + @STR_COMMAND_SET_IDENTITY + '*/'

    FETCH NEXT FROM CUR_DISABLE_CONSTRAINT INTO @STR_TABLE_NAME, @STR_COMMAND_SET_IDENTITY, @STR_COMMAND
  END -- WHILE

  CLOSE CUR_DISABLE_CONSTRAINT
 
  DEALLOCATE CUR_DISABLE_CONSTRAINT
 
  PRINT LTRIM(RTRIM('Table string list: ' + @STR_TMP))

  --SET NOCOUNT OFF
END

Ad esempio, il comando:

EXEC CS_SP_ENABLE_DISABLE_FK_CONSTRAINT_ON_TABLE 'TAB_A, TAB_B', 0, -1

permette di disabilitare i foreign key constraint e permette l'assegnazione di valori ai campi IDENTITY.

Viceversa il comando:

EXEC CS_SP_ENABLE_DISABLE_FK_CONSTRAINT_ON_TABLE 'TAB_A, TAB_B', -1, 0

riprinstina l'abilitazione per foreign key constraint e impedisce l'assegnazione di valori ai campi IDENTITY.

Ciao !