Cascade delete option in SQL Server - Deleting multiple table records

CASCADE DELETE OPTION IN SQL SERVER:

I have seen lot of SQL developers were struggling to delete multiple tables simultaneously.

Scenario: I have deleted the master table and I would like delete those records in the child tables. Is it possible to delete multiple tables at the same time. SQL Server doesn't have an option to delete multiple tables at the same time. As a work around, there are some other options we can do. Below are the options,

1. Place the delete statements inside a transaction. Delete the child table first followed by master table and afterwards commit the transaction.

2. Create a trigger on the master table which will get invoked on delete operation. It will automatically delete the child tables data.

3. Third option is, Cascade delete option. This will be set during the Foreign key relation creation stage.

Below is a detailed article on Option 3

Primary Key : A column which helps us to uniquely identify a record in the table.

Foreign Key : A column which is referring a primary key on the other table and holds reference of a remote table record.

-- Am creating a table with ID as primary key.

This table holds primary key on the column ID.

CREATE TABLE [dbo].[VENKAT_TABLE](
[ID] [int] NOT NULL,
[NAM] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
[NAME1] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_VENKAT_TABLE] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

-------------------------------------------------------------------------

--Creating second table with ID column in the previous table as reference

This table mapped to a foreign key which is a primary key in another table with option Cascade delete.

CREATE TABLE [dbo].[VENKAT_TABLE1](
[ID] [int] NOT NULL,
[NAM] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_VENKAT_TABLE1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [master]
GO
ALTER TABLE [dbo].[VENKAT_TABLE1] WITH CHECK ADD CONSTRAINT [FK_VENKAT_TABLE1_VENKAT_TABLE] FOREIGN KEY([ID])
REFERENCES [dbo].[VENKAT_TABLE] ([ID])

ON DELETE CASCADE

-------------------------------------------------------------------------

-- Create third table with ID in the Frist table as reference

This table mapped to a foreign key which is a primary key in another table with option Cascade delete.

GO
CREATE TABLE [dbo].[VENKAT_TABLE2](
[ID] [int] NOT NULL,
[NAM] [varchar](100) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [master]
GO
ALTER TABLE [dbo].[VENKAT_TABLE2] WITH CHECK ADD CONSTRAINT [FK_VENKAT_TABLE2_VENKAT_TABLE] FOREIGN KEY([ID])
REFERENCES [dbo].[VENKAT_TABLE] ([ID])
ON DELETE CASCADE

-------------------------------------------------------------------------

OOPS, in the third SQL Statement, I got the below error. Why?

"The ALTER TABLE statement conflicted with the FOREIGN KEY constraint"

Reason: Third table has some data whereas First table doesn't have any data. So, we can't create any reference at this stage. I have deleted all the records and recreated the relation ship. Yes, am able to create it.

----------------------------------------------------------------------------

INSERT INTO VENKAT_TABLE VALUES(1,'VENKAT','JAYAKANTHAM')
INSERT INTO VENKAT_TABLE VALUES(2,'SUBA','SUNDARESAN')
INSERT INTO VENKAT_TABLE VALUES(3,'KRISHIV','VENKAT')

INSERT INTO VENKAT_TABLE1 VALUES(1,'VENKAT')
INSERT INTO VENKAT_TABLE1 VALUES(2,'SUBA')
INSERT INTO VENKAT_TABLE1 VALUES(3,'KRISHIV')

INSERT INTO VENKAT_TABLE2 VALUES(1,'VENKAT')
INSERT INTO VENKAT_TABLE2 VALUES(2,'SUBA')
INSERT INTO VENKAT_TABLE2 VALUES(3,'KRISHIV')

--- JOINING THE TABLE USING THEIR RELATIONSHIPS --------------------
SELECT * FROM VENKAT_TABLE A INNER JOIN VENKAT_TABLE1 B ON A.ID=B.ID
INNER JOIN VENKAT_TABLE2 C ON B.ID=C.ID

------------ DELETE MASTER TABLE WILL DELETE THE OTHER TABLE DUE TO CASCADE DELETE OPTION IN FOREIGN KEY CONSTRAINT CREATION.------------

DELETE VENKAT_TABLE
FROM VENKAT_TABLE A INNER JOIN VENKAT_TABLE1 B ON A.ID=B.ID
INNER JOIN VENKAT_TABLE2 C ON B.ID=C.ID


select * from VENKAT_TABLE -- Will provide empty records
select * from VENKAT_TABLE1 -- Will provide empty records
select * from VENKAT_TABLE2 -- Will provide empty records

----------------------------------------------------------------------------
Similarly, we can do this for update operations too. Updating the master table will get reflected in the child table.

Cheers,
Venkatesan Prabu .J