Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Deleting Data in Foreign Key Order - SQL Server

RSS
Modified on Tue, Jan 11, 2022, 12:35 PM by Administrator Categorized as SQL Server
This page is part of the Foreign Key Pages collection.
Click the icon to see the index.

Overview

Based on the foreign keys in the database, the following SQL generates a list of DELETE statements in the order they should be executed to avoid any issues with foreign key constraints.

Code

declare
     @RootTable     nvarchar(1000)
    ,@RootColumn    nvarchar(1000)
    ,@FilterClause  nvarchar(1000)

select
     @RootTable     = 'Tenant'
    ,@RootColumn    = 'Id'
    ,@FilterClause  = 'in (10)'

;with MyRecursion as (

    /*--- Base Case ---*/
    SELECT
         TableName      = [name]
        ,Lvl            = 1
        ,WhereClause    = convert(nvarchar(4000), ' where [' + @RootColumn + '] ' + @FilterClause)
        ,ParentTable    = convert(nvarchar(1000), null)
        ,DepPath        = convert(varchar(8000), '/' + [name])
    FROM    
        sys.tables
    where 1=1
        and [name] = @RootTable

    /*--- Recursive Case ---*/
    union all SELECT
         TableName      = f.OnTable
        ,Lvl            = r.Lvl + 1
        ,WhereClause    = convert(nvarchar(4000),
                            case r.Lvl when 1 THEN
                                ' where ['
                                    + f.OnColumn
                                    + '] '
                                    + @FilterClause
                            else
                                ' where [' 
                                    + f.OnColumn 
                                    + '] in (select [' 
                                    + f.AgainstColumn + '] from [' 
                                    + f.AgainstTable 
                                    + '] ' 
                                    + r.WhereClause 
                                    + ')'
                            end
                            )
        ,ParentTable    = convert(nvarchar(1000), f.AgainstTable)
        ,DepPath        = convert(varchar(8000), r.DepPath + '/' + f.OnTable )
    FROM    
        dbo.FKeys f
        inner join MyRecursion r
            on f.AgainstTable = r.TableName

            /* TODO: Exclude entities we want to keep */
            and f.OnTable not in ('AspNetUsers','AspNetRoles')
            
            /* Avoid circular references */
            and r.DepPath not like '%/' + f.OnTable + '/%'

)
,DeleteStatements as (
    SELECT
         Lvl        = max(Lvl)
        ,TableName
        ,SqlCode    = 'delete from [' + TableName + ']' + WhereClause
    FROM    
        MyRecursion
    WHERE 1=1
        and TableName not in ('Lab')
    group BY    
        TableName
        ,WhereClause
    )
select
    *
    --distinct TableName, Lvl
FROM
    DeleteStatements
order by 
     Lvl desc
    ,TableName

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2022, Patrick Jasinski.