In this article i'm going to explain "How to delete multiple records from different tables based on parent table in sql server" as i shown in above image. First we need to create this 3 tables i.e { User Table , Fruit Table and UserFruitMapping Table }.
Create Database : User Table
I'm creating this table in master database
Create Database : 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 Database : UserFruitMapping Table
In this table i'm using FOREIGN KEY WITH DELETE CASCADE.
What is FOREIGN KEY ON DELETE CASCADE
In general if the parent table record is having a reference in the child table, we can't delete the record directly. With the use of "ON DELETE CASCADE", when we delete the parent table record, that record and all its child references will be deleted at once.
Create StoredProcedure to Delete Record
Create a simple stored procedure to delete record simply by passing userID.
Just we need to pass UserID to this StoredProcedure "spDeleteFruitRecord".
you get result as i shown in above image