Cascade Delete in SQL Server

Recently I came up with a scenario to delete the records from a table.
I thought its simple. I just wrote a simple delete statement like

DELETE TABLE TableA;

But it throws me an error message as

The DELETE statement conflicted with the REFERENCE constraint "FK__TableA1__AID__2D47B39A". 
The conflict occurred in database "AdventureWorks2008R2", table "dbo.TableA1", column 'AID'

So what should I do? I have to delete the associated records from the child tables before attempting a Delete on the parent table.

So I just deleted the records from Child table as,

DELETE TABLE TableA1;

Now again I attempted to delete the records from the parent table.

DELETE TABLE TableA;

Again got the same error message.

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__TableA2__AID__2F2FFC0C". 
The conflict occurred in database "AdventureWorks2008R2", table "dbo.TableA2", column 'AID'.
The statement has been terminated.

This time on a different Table – TableA2

So you can view the dependencies on a table by using the Post mentioned HERE
and you can manually write the delete statements for the Child tables as per the relation.

If it has 3-4 Child tables, then it is okay to write the delete scripts manually.

What if you have a table associate with more than 10 child tables.
It involves lot of manual work to delete one by one to delete the final data from the parent table.

So lets see how to simplify this using a Stored Procedure.

I just started searching whether anyone have simplified these kind of tasks and came across lot of solutions.

One interesting thread in StackOverFlow is HERE

So Before Going to run the SP, first create the sample tables and records for reproducing this scenario.

CREATE Table TableA
(
ID int,
Name varchar(20),
Primary Key(ID)
)

Create Table TableA1
(
ID int,
Name Varchar(20),
AID int Foreign Key references TableA(ID)
)

Create Table TableA2
(
ID int,
Name varchar(20),
AID int Foreign key references TableA(ID)
)

INSERT into TableA values(1, 'A')
INSERT into TableA values(2, 'B')
INSERT into TableA values(3, 'C')

Insert into TableA1 VALUES (1, 'A', 1)
Insert into TableA1 VALUES (2, 'A', 2)
Insert into TableA1 VALUES (3, 'A', 3)

Insert into TableA2 VALUES (1, 'A', 1)
Insert into TableA2 VALUES (2, 'A', 2)
Insert into TableA2 VALUES (3, 'A', 3)

Now Create the required Functions and procedures by running the following script.

IF OBJECT_ID('dbo.udfGetFullQualName') IS NOT NULL
    DROP FUNCTION dbo.udfGetFullQualName;
GO

CREATE FUNCTION dbo.udfGetFullQualName
(@ObjectId INT)
RETURNS VARCHAR (300)
AS
BEGIN
	SET ANSI_NULLS ON
	SET QUOTED_IDENTIFIER ON
    DECLARE @schema_id AS BIGINT;
    SELECT @schema_id = schema_id
    FROM   sys.tables
    WHERE  object_id = @ObjectId;
    RETURN '[' + SCHEMA_NAME(@schema_id) + '].[' + OBJECT_NAME(@ObjectId) + ']';
END
GO

--============ Supporting Function dbo.udfGetOnJoinClause
IF OBJECT_ID('dbo.udfGetOnJoinClause') IS NOT NULL
    DROP FUNCTION dbo.udfGetOnJoinClause;
GO

CREATE FUNCTION dbo.udfGetOnJoinClause
(@fkNameId INT)
RETURNS VARCHAR (1000)
AS
BEGIN
	SET ANSI_NULLS ON
	SET QUOTED_IDENTIFIER ON
    DECLARE @OnClauseTemplate AS VARCHAR (1000);
    SET @OnClauseTemplate = '[<@pTable>].[<@pCol>] = [<@cTable>].[<@cCol>] AND ';
    DECLARE @str AS VARCHAR (1000);
    SET @str = '';
    SELECT @str = @str + REPLACE(REPLACE(REPLACE(REPLACE(@OnClauseTemplate, '<@pTable>', OBJECT_NAME(rkeyid)), '<@pCol>', COL_NAME(rkeyid, rkey)), '<@cTable>', OBJECT_NAME(fkeyid)), '<@cCol>', COL_NAME(fkeyid, fkey))
    FROM   dbo.sysforeignkeys AS fk
    WHERE  fk.constid = @fkNameId; --OBJECT_ID('FK_ProductArrearsMe_ProductArrears')
    RETURN LEFT(@str, LEN(@str) - LEN(' AND '));
END
GO

--=========== CASECADE DELETE STORED PROCEDURE dbo.uspCascadeDelete
IF OBJECT_ID('dbo.uspCascadeDelete') IS NOT NULL
    DROP PROCEDURE dbo.uspCascadeDelete;

GO
CREATE PROCEDURE dbo.uspCascadeDelete
@ParentTableId VARCHAR (300), @WhereClause VARCHAR (2000), @ExecuteDelete CHAR (1)='Y', --'N' IF YOU NEED DELETE SCRIPT
@FromClause VARCHAR (8000)='', @Level INT=0 -- TABLE NAME OR OBJECT (TABLE) ID (Production.Location) WHERE CLAUSE (Location.LocationID = 7) 'Y' IF WANT TO DELETE DIRECTLY FROM SP,  IF LEVEL 0, THEN KEEP DEFAULT
AS 
SET NOCOUNT ON;
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON

DECLARE @DebugMsg AS VARCHAR (4000), 
@DebugIndent AS VARCHAR (50);
SET @DebugIndent = REPLICATE('---', @@NESTLEVEL) + '> ';
IF ISNUMERIC(@ParentTableId) = 0
    BEGIN 
        IF CHARINDEX('.', @ParentTableId) = 0
            SET @ParentTableId = OBJECT_ID('[dbo].[' + @ParentTableId + ']');
        ELSE
            SET @ParentTableId = OBJECT_ID(@ParentTableId);
    END
IF @Level = 0
    BEGIN
        PRINT @DebugIndent + ' **************************************************************************';
        PRINT @DebugIndent + ' *** Cascade delete ALL data from ' + dbo.udfGetFullQualName(@ParentTableId);
        IF @ExecuteDelete = 'Y'
            PRINT @DebugIndent + ' *** @ExecuteDelete = Y *** deleting data...';
        ELSE
            PRINT @DebugIndent + ' *** Cut and paste output into another window and execute ***';
    END
DECLARE @CRLF AS CHAR (2);
SET @CRLF = CHAR(13) + CHAR(10);
DECLARE @strSQL AS VARCHAR (4000);
IF @Level = 0
    SET @strSQL = 'SET NOCOUNT ON' + @CRLF;
ELSE
    SET @strSQL = '';
SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST (@@NESTLEVEL AS VARCHAR) + '''';
IF @ExecuteDelete = 'Y'
    EXECUTE (@strSQL);
ELSE
    PRINT @strSQL;
DECLARE curs_children CURSOR LOCAL FORWARD_ONLY
    FOR SELECT DISTINCT constid AS fkNameId, -- constraint name
                        fkeyid AS cTableId
        FROM   dbo.sysforeignkeys AS fk
        WHERE  fk.rkeyid <> fk.fkeyid -- DO NOT HANDLE self referencing tables!!!
               AND fk.rkeyid = @ParentTableId;
OPEN curs_children;
DECLARE @fkNameId AS INT, 
@cTableId AS INT, 
@cColId AS INT, 
@pTableId AS INT, 
@pColId AS INT;
FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId; --, @cColId, @pTableId, @pColId
DECLARE @strFromClause AS VARCHAR (1000);
DECLARE @nLevel AS INT;
IF @Level = 0
    BEGIN
        SET @FromClause = 'FROM ' + dbo.udfGetFullQualName(@ParentTableId);
    END
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @strFromClause = @FromClause + @CRLF + '      INNER JOIN ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + '       ON ' + dbo.udfGetOnJoinClause(@fkNameId);
        SET @nLevel = @Level + 1;
        EXECUTE dbo.uspCascadeDelete @ParentTableId = @cTableId, @WhereClause = @WhereClause, @ExecuteDelete = @ExecuteDelete, @FromClause = @strFromClause, @Level = @nLevel;
        SET @strSQL = 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + @CRLF + @strFromClause + @CRLF + 'WHERE   ' + @WhereClause + @CRLF;
        SET @strSQL = @strSQL + 'PRINT ''---' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@cTableId) + '     Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF + @CRLF;
        IF @ExecuteDelete = 'Y'
            EXECUTE (@strSQL);
        ELSE
            PRINT @strSQL;
        FETCH NEXT FROM curs_children INTO @fkNameId, @cTableId;
    --, @cColId, @pTableId, @pColId
    END
IF @Level = 0
    BEGIN
        SET @strSQL = @CRLF + 'PRINT ''' + @DebugIndent + dbo.udfGetFullQualName(@ParentTableId) + ' Level=' + CAST (@@NESTLEVEL AS VARCHAR) + ' TOP LEVEL PARENT TABLE''' + @CRLF;
        SET @strSQL = @strSQL + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' WHERE ' + @WhereClause + @CRLF;
        SET @strSQL = @strSQL + 'PRINT ''' + @DebugIndent + 'DELETE FROM ' + dbo.udfGetFullQualName(@ParentTableId) + ' Rows Deleted: '' + CAST(@@ROWCOUNT AS VARCHAR)' + @CRLF;
        IF @ExecuteDelete = 'Y'
            EXECUTE (@strSQL);
        ELSE
            PRINT @strSQL;
    END
CLOSE curs_children;
DEALLOCATE curs_children;
GO

----------###########################################################################---------------------------
----------###########################################################################---------------------------
----------###########################################################################---------------------------
IF OBJECT_ID('dbo.CascadeDelete') IS NOT NULL
  DROP Procedure dbo.CascadeDelete;
GO
----------###########################################################################---------------------------
----------###########################################################################---------------------------
----------###########################################################################---------------------------

Now the required Functions and Stored Procedures has been created and we are ready to go.

Now to delete the data from the parent table without any condition,
Run the following script

EXEC uspCascadeDelete 'TableA', '1=1'

You can also use condition in your delete statements
The following delete statements can be easily re written using

DELETE FROM TABLEA WHERE ID = 1

as

EXEC uspCascadeDelete 'TableA', 'ID=1'

Advertisement

Finding Out Object Dependencies in SQL Server

Lets see how to find the object Dependencies in SQL Server using SSMS.
We have a simple inbuilt option in SSMS to view the object dependencies in SQL Server.
Before that, Why we need to find out the object dependencies in SQL Server.

Suppose we have a table called Table A and if we are going to delete some rows
out of that table, then we may be having the foreign key constraint associated with Child Tables.
It will throw you an error related to foreign key constraint and you cant be able to proceed further.

For Example I am using AdventureWorks database and I am trying to delete the data from the Person Table.

DELETE FROM [AdventureWorks2008R2].[Person].[Person]

You will be getting the following error from the Sql Server

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_EmailAddress_Person_BusinessEntityID". 
The conflict occurred in database "AdventureWorks2008R2", table "Person.EmailAddress", column 'BusinessEntityID'.
The statement has been terminated.

Now from this message it is clear that there is foreign key reference with table Person.EmailAddress.

So you can’t be able to delete data from Person.Person without deleting the data from Person.EmailAddress
But now we are not sure that how many objects [tables/views] has a dependency on the table Person.Person

So to find out this, we have simple provision in Sql Server.

Open the Object Explorer in SSMS.
Go to the Database that you want and select the Table you want to view dependencies.
Right Click on that Table [Person].[Person] and Select “View Dependencies”

Now this will open a window which will show you the Objects that depends on the table Person.Person.

As you see the Tables BusinessEntityContact, Email Address, Employee and IUPerson Trigger and some other Views and Tables depends on the object “Person”.

So you can’t be able to delete the rows in Person without Deleting the data in the dependent Objects.
Now Select the Option, “Objects on which [Person] Depends”

This will list out the Objects that “Person” depends on.