Abilitare e disabilitare i constraints

Sempre nell’ambito di una migrazione talvolta è comodo disabilitare temporaneamente i check constraints e le foreign keys relativi alla/e tabelle di un database, per poter effettuare delle operazioni di manutenzione estemporanee o cancellazioni e reinserimenti multipli.

E’ altresì comoda la disabilitazione temporanea quando si deve svuotare (totalmente o parzialmente) una tabella per effettuarne il carico tramite BCP. Dopo aver disabilitato le foreign keys, visto che comunque non è possibile effettuare il truncate (essendo queste ultime ancora presenti sebbene disabilitate), è possibile effettuare le DELETE selettive o sull’intera tabella.  Ovviamente in presenza di milioni di righe nella tabella consiglio di SCRIPTARE i constraints, DROPPARLI, effettuare il TRUNCATE, e RICREARLI.

Se volessimo effettuare la disabilitazione complessiva dei constraints su tutto il DB basterebbe eseguire la seguente stored procedure:

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

e per la riabilitazione:

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Se viceversa volessimo disabilitare tutti i constraint di una singola tabella dovremmo usare:

ALTER TABLE tableName NOCHECK CONSTRAINT ALL

e per l’abilitazione:

ALTER TABLE tableName CHECK CONSTRAINT ALL

Di seguito riporto uno script TSQL che genera il codice per disabilitare selettivamente ogni singolo constraint del DB:

DECLARE @String as varchar(max)
SELECT @String = ''+char(13)+char(10);

SELECT @String = @String +
   'ALTER TABLE ['+schema_name(so.schema_id)+'].['+object_name(so.parent_object_id)+
   '] NOCHECK CONSTRAINT [' + so.name +']' + char(13)+char(10)
FROM sys.objects so
where so.type in ('F')

SELECT @String = @String + char(13)+char(10)

SELECT
@String = @String +
   'DELETE FROM ['+schema_name(so.schema_id)+'].['+ so.name + ']'+char(13)+char(10)
FROM sys.objects so
where so.type in ('U')

SELECT @String = @String + char(13)+char(10)

SELECT @String = @String +
   'ALTER TABLE ['+schema_name(so.schema_id)+'].['+object_name(so.parent_object_id) +
   '] CHECK CONSTRAINT [' + so.name +']' + char(13)+char(10)
FROM sys.objects so
where so.type in ('F')

SELECT  1 AS TAG, 0 AS PARENT, @String
            as [SqlCommand!1!!cdata]
FOR XML
EXPLICIT, xmldata

Viene generata una riga XML (unico tipo di dati testuale che viene visualizzato per intero nel Management Studio), dentro alla sezione CDATA troviamo lo script TSQL suddiviso in tre parti:

• vengono dapprima disabilitati i constraints (ALTER TABLE … NOCHECK …)
• poi vengono effettuate le operazioni, in questo caso DELETE
• vengono riabilitati i constraints (è stato modificata l’istruzione da NOCHECK a CHECK)

Riporto anche uno stralcio dell’output

ALTER TABLE [dbo].[Applicazioni] NOCHECK CONSTRAINT [FK_Applicazioni_Ambiti]
ALTER TABLE [dbo].[Applicazioni] NOCHECK CONSTRAINT [FK_Applicazioni_Fornitori]

DELETE FROM [dbo].[Ambiti]
DELETE FROM [dbo].[Fornitori]
DELETE FROM [dbo].[Applicazioni]

ALTER TABLE [dbo].[Applicazioni] CHECK CONSTRAINT [FK_Applicazioni_Ambiti]
ALTER TABLE [dbo].[Applicazioni] CHECK CONSTRAINT [FK_Applicazioni_Fornitori]