Efficient Deletion of Multiple Records in SQL Server Using CASCADE and Stored Procedures

Introduction

Unlock the power of SQL Server with our step-by-step guide on efficiently deleting multiple records from different tables based on a parent table. The process involves creating and utilizing three tables - User, Fruit, and UserFruitMapping. Follow along as we explore the database setup, FOREIGN KEY with DELETE CASCADE, and a handy stored procedure for seamless record deletion.

Creating the User Table:

I'm creating this table in master database

 Create Table User(
 @UserID int Primary Key,
 @Username varchar(50))

//Inserting Records

 Insert into User values('1','John')
 Insert into User values('2','Amir')
 Insert into User values('3','Robert')

Creating the Fruit Table:

I'm using this table to delete multiple records which available in UserFruitMapping Table as i shown in above image output result.

 Create Table Fruit(
 @FruitID int Primary Key,
 @FruitName varchar(50))

//Inserting Records 

 Insert into Fruit values('f1', 'Grapes')
 Insert into Fruit values('f'2', 'Banana')
 Insert into Fruit values('f'3', 'Mango')
 Insert into Fruit values('f4', 'PineApple')
 Insert into Fruit values('f5', 'Sapota')
 Insert into Fruit values('f6', 'Kiwi')
 Insert into Fruit values('f7', 'Apple')

Creating the UserFruitMapping Table with CASCADE:

In this table i'm using FOREIGN KEY WITH DELETE CASCADE.

 Create Table Fruit(
 @UFID int Primary Key,
 @FruitID int,
 @UserID int
 contraint fk_UserFruitMapping_Fruit FOREIGN KEY (FruitID) 
  References Fruit(FruitID) On Delete Cascade
 contraint fk_UserFruitMapping_User FOREIGN KEY (UserID) 
  References User(UserID)
)

//Inserting Records 

 Insert into UserFruitMapping values('uf1',f1','1')
 Insert into UserFruitMapping values('uf2',f2','1')
 Insert into UserFruitMapping values('uf3',f3','1')
 Insert into UserFruitMapping values('uf4',f4','1')
 Insert into UserFruitMapping values('uf5',f5','2')
 Insert into UserFruitMapping values('uf6',f6','2')
 Insert into UserFruitMapping values('uf7',f7','3')

Understanding FOREIGN KEY ON DELETE CASCADE:

When a parent table record has references in the child table, the CASCADE ensures that deleting the parent record also deletes all its child references simultaneously.

Creating the StoredProcedure for Deletion:

Create a simple stored procedure to delete record simply by passing userID.

 create procedure spDeleteFruitRecord( @UserID int )
 As
  Begin
   delete Fruit where FruitID IN (Select FruitID from UserFruitMapping where UserID=@UserID 
  End

Executing the StoredProcedure:

Simply pass the UserID to the stored procedure spDeleteFruitRecord to initiate record deletion.

 Exec spDeleteFruitRecord 1

Conclusion:

Efficiently managing and deleting multiple records in SQL Server is simplified with CASCADE and stored procedures. Implement this solution in your database, and download the source code here to streamline your record management effortlessly.