Safely empty or fill tables without violating any foreign key relationship

Le relazioni tra le tabelle di un database sono il meccanismo necessario a garantire l’integrità del dato.

Una tabella “figlia” non potrà, se opportunamente relazionata, contenere righe che possano legarsi a righe NON esistenti sulla tabella “padre” (Ad esempio: è impossibile che esistano delle righe di una fattura relative ad una riga di intestazione che non esiste).

Il fatto di avere dei legami tra questi oggetti evita anche, di conseguenza, che io possa cancellare delle righe “padre” che abbiano, in tabelle “figlie”, tuple che siano ad esse referenziate.

Per questo motivo è utile poter avere un modo per recuperare una sorta di elenco di tutte le tabelle, ordinato secondo il naturale ordine di relazione: prima le tabelle padre e quella senza relazioni, dopo le tabelle figlie (o figlie di figlie):

Nota bene: nell’esempio sopra la tabella [product] NON è volontariamente legata alla tabella [invoiceRow] solo per scopo di demo.

Avere questo ordinamento delle tabelle può esserci utile in due scenari:

• popolare un database nell’ordine corretto (safely fill), ad esempio per avere una mole di dati simile a quella di produzione per effettuare verifiche di performance
• svuotare un database nell’ordine corretto (safely empty), ad esempio per riportare le strutture al loro stato originario dopo una fase di test

Di seguito, grazie al meccanismo delle CTE ricorsive, un esempio di implementazione per ottenere il nostro ordinamento:

 WITH cteTables
    ( 
       tableName , 
        TableID , 
        ordinal 
    )
AS 
( 
    /* find all tables - anchor member for CTE */
    SELECT DISTINCT
       QUOTENAME(OBJECT_SCHEMA_NAME( o.id )) + '.' + QUOTENAME(OBJECT_NAME( o.id )) AS tableName, 
       o.id AS TableID , 
       0 AS ordinal
    FROM dbo.sysobjects o 
    INNER JOIN sys.all_columns c ON o.ID = c.object_id
    WHERE o.type = 'U'
        
    UNION ALL
        
    /* recursive member */
    SELECT 
       QUOTENAME(OBJECT_SCHEMA_NAME( o.id )) + '.' + QUOTENAME(OBJECT_NAME( o.id )) AS tableName, 
       o.id AS TableID , 
        cte.ordinal + 1 AS ordinal
    FROM dbo.sysobjects o 
    INNER JOIN sys.foreign_keys fk ON fk.parent_object_id = o.id AND fk.parent_object_id != fk.referenced_object_id
    INNER JOIN cteTables cte ON fk.referenced_object_id = cte.TableID
    WHERE o.type = 'U' 
),
cteFinal as
(
    SELECT DISTINCT 
          t.ordinal, 
          t.tableName
    FROM cteTables t 
    INNER JOIN
    ( 
       SELECT 
          tableName, 
          MAX( ordinal ) AS ordinal
       FROM cteTables
       GROUP BY tableName
    ) cte ON t.tableName = cte.tableName AND t.ordinal = cte.ordinal
    WHERE t.tableName not in
    (
       /* i need to eliminate tables like sysdiagrams, dtproperties, ... */
       SELECT QUOTENAME( [schema] ) + '.' + QUOTENAME( name )AS tableName
        FROM( 
             SELECT SCHEMA_NAME( t0.schema_id )AS [schema] , 
                   name , 
                   (
                    SELECT MAJOR_ID
                    FROM SYS.EXTENDED_PROPERTIES WITH ( NOLOCK )
                    WHERE 
                       MAJOR_ID = t0.object_id AND 
                       MINOR_ID = 0 AND 
                       CLASS = 1 AND 
                       NAME = N'MICROSOFT_DATABASE_TOOLS_SUPPORT'
                    ) AS MS_ID
               FROM sys.tables t0 )t1
        WHERE MS_ID IS NOT NULL
    )
 )
SELECT 
    ordinal,
    tableName 
FROM cteFinal    
ORDER BY
    ROW_NUMBER() OVER(ORDER BY ordinal , tableName)

Eseguendo il codice T-SQL sul database visualizzato in figura:

A questo punto, se volessi popolare il database (con dati dummy, di test), posso utilizzare la procedura già postata qui sfruttando lo stesso ordine riportato dal codice mandato in esecuzione.

Ad esempio:

 EXEC dbo.up_insertDummyDataWithFK @schemaName = 'dbo', @tableName = 'invoiceHeader' , @numberOfRows = 2;
EXEC dbo.up_insertDummyDataWithFK @schemaName = 'dbo', @tableName = 'product' , @numberOfRows = 2;
EXEC dbo.up_insertDummyDataWithFK @schemaName = 'dbo', @tableName = 'invoiceRow' , @numberOfRows = 2;
EXEC dbo.up_insertDummyDataWithFK @schemaName = 'dbo', @tableName = 'invoiceRowDetail' , @numberOfRows = 2;

Vedo i dati:

 select * from [dbo].[invoiceHeader]
select * from [dbo].[product]
select * from [dbo].[invoiceRow]
select * from [dbo].[invoiceRowDetail]

Se volessi, invece, svuotare le tabelle dovrei utilizzare l’ordine inverso (quindi dall’ultima figlia, fino al padre).

Ad esempio:

 delete from [dbo].[invoiceRowDetail]
delete from [dbo].[invoiceRow]
delete from [dbo].[product]
delete from [dbo].[invoiceHeader]