SQLTeam.com | Weblogs | Forums

How to perform Cascade delete on multiple foreign keys

Greetings experts,

We have a web app with three sections.

The first section contains information about Speakers. The owner of the app usually inserts a Speaker record into the Speakers table first.

Then the second section contains sermons related to the speaker. To add sermons to the database, owner has to select the speaker who preached the sermon from the Speakers table hence creating a relationship between the Speakers table and Sermons table.

Finally, there is a third table called ShootLog. To create a log, the owner selects some Speaker info from Speakers table, some Sermon info from Sermons table and the rest from ShootLog to insert into ShootLog table thereby creating a relationship between Speakers table, Sermons table and ShootLog table.

So far so good.

The challenge we have now is that if the owner wishes to delete some records on any of the tables, we would like to create cascading delete whereby if a record is deleted from ShootLog table, the associated record in Sermons table and Speakers must be deleted as well to avoid creating data inconsistency.

Same with Sermons and Speakers tables.

Rather than doing this programmatically, I thought it would be better and more reliable to handle this on the database side.

I tried with the following DDL below but it doesn't work.

Any ideas what I am missing?

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Speakers](
	[SpeakerID] [int] IDENTITY(1,1) NOT NULL,
	[SpeakerName] [nvarchar](75) NULL,
	[ClientName] [nvarchar](75) NULL,
	[Email] [nvarchar](50) NULL,
	[dateAdded] [datetime] NULL,
	[WebsiteURL] [nvarchar](250) NULL,
	[MinistryName] [nvarchar](150) NULL,
 CONSTRAINT [PK_Speakers] PRIMARY KEY CLUSTERED 
(
	[SpeakerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

***************************************************************

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Sermons](
	[sermonID] [int] IDENTITY(1,1) NOT NULL,
	[SpeakerID] [int] NULL,
	[ClientName] [nvarchar](75) NULL,
	[dateFilmed] [datetime] NULL,
	[sermonTitle] [nvarchar](50) NULL,
	[Scripture] [nvarchar](50) NULL,
	[VideoURL] [nvarchar](50) NULL,
	[SermonNotes] [nvarchar](max) NULL,
 CONSTRAINT [PK_Sermons] PRIMARY KEY CLUSTERED 
(
	[sermonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Sermons]  WITH CHECK ADD  CONSTRAINT [FK_Sermons] FOREIGN KEY([SpeakerID])
REFERENCES [dbo].[Speakers] ([SpeakerID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Sermons] CHECK CONSTRAINT [FK_Sermons]
GO

**********************************************

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ShootLog](
	[LogID] [int] IDENTITY(1,1) NOT NULL,
	[SpeakerID] [int] NULL,
	[SermonID] [int] NULL,
	[BroadcastDate] [datetime] NULL,
	[BroadcastLength] [nvarchar](50) NULL,
	[FacebookViewers] [numeric](18, 2) NULL,
	[Translation] [nvarchar](50) NULL,
 CONSTRAINT [PK_ShootLog] PRIMARY KEY CLUSTERED 
(
	[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ShootLog]  WITH CHECK ADD  CONSTRAINT [FK_ShootSpeakerLog] FOREIGN KEY([SermonID])
REFERENCES [dbo].[Sermons] ([sermonID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[ShootLog] CHECK CONSTRAINT [FK_ShootSpeakerLog]
GO

What do you mean "it doesn't work"? What error did you get? Can you provide some usable sample data that gets the error? That way we can see the error too and provide code to resolve it

Hi Scott,

Thank you for your prompt response.

Ok, as stated, we have a web app set up to use the CRUD matrix. Users can insert records, update records, delete records using our web app.

So, what I mean by it doesn't work is that if you look at the CREATE TABLE scripts I posted, you can see that I have altered ShootLog and Sermons tables to add Cascade Delete and if what I read is correct, when I click Delete record button on the ShootLog Section, I expected that row of records which is includes Speaker info (from Speakers table) and Sermon info (from Sermons table) along with ShootLog data to be deleted but nothing got deleted from any of the tables.
Please see attached image.

As you can see, there are three sections, Top section contains speakers info from Speakers table.

Middle section contains info from Sermons table as well as info pulled from Speakers table.

Bottom section is for ShootLog table but also contains info pulled from Sermons table as well as Speakers table.

Also, please notice the Update and Delete buttons at far right of each section.

If for instance we wish to delete the records from Speakers table, we can do so from the top section and ensure that all the associated records from Sermons and ShootLog tables are also deleted.

That's what we are trying to accomplish.

Did you run a direct query to see if data actually got deleted from the tables? I'm not sure the gui screen will necessary reflect deletes, esp. in the other related tables.

If you delete any records, the gui immediately refreshes to reflect the deletes.

All this was working correctly until I altered the tables to add the cascade delete.

In fact, the screenshot shows three rows of records on each section.

If the cascade delete works, it should delete one row from each section.

All I would really appreciate is for an expert like you from a DB standpoint, to confirm that the way I have the cascade delete on the tables is correct.

Once I can confirm that I did them correctly on the tables, then I can focus on finding out why the delete action stopped working on my app.

Thanks again for your help

Again, need usable data to test with to confirm. Someone might come along who will create test data for your tables themselves, but I just don't have time to do that. I sometimes volunteer my time to writ SQL to help with a dozen or more qs a day, but I don't have time to be everyone's data secretary too.

screen shots are nice but since we do not have the application running locally, you are going to have to provide some sample data we can test with to provide you an answer. help us help you.

Ok Scott, how do you want the usable data?

My app is on my PC.

I thought someone could just create table that references foreign key with cascade delete.

But we can't test delete with only a table definition and no data, since there's nothing to DELETE :grinning:

1 Like

No problem sir,

How do you want the data?

INSERT INTO dbo.table_name1 VALUES(...row1...),(...row2...),...
INSERT INTO dbo.table_name2 VALUES(...row1...),(...row2...),...
INSERT INTO dbo.table_name3 VALUES(...row1...),(...row2...),...

I hope this one data sampling of each table is ok:

INSERT INTO [dbo].[Speakers]
           ([SpeakerID]   --PK
           ,[SpeakerName]
           ,[ClientName]
           ,[Email]
           ,[dateAdded]
           ,[WebsiteURL]
           ,[MinistryName])
     VALUES
	       (101
           ,'John Doe'
           ,'Kent Dunn'
           ,'john.doe@yahoo.com'
           ,'01/20/2021'
           ,'https://youtube.com'
           ,'Community Church')

INSERT INTO [dbo].[Sermons]
           ([SermonID]  --PK
           .[SpeakerID]  --FK to Speakers table
           ,[ClientName]
           ,[dateFilmed]
           ,[sermonTitle]
           ,[Scripture]
           ,[VideoURL]
           ,[SermonNotes])
     VALUES
           (1
		   ,101
           ,'Marty Dunn'
           ,'02/12/2021'
           ,'Thou shall sow love'
           ,'John 24'
           ,'https://youtube.com'
           ,'his is a test of Sermon grid')

INSERT INTO [dbo].[ShootLog]
           ([LogID]        --PK
		   ,[SpeakerID]    --FK to Speakers table  
           ,[SermonID]     --FK to Sermons table
           ,[BroadcastDate]
           ,[BroadcastLength]
           ,[FacebookViewers]
           ,[Translation])
     VALUES
           (3005
		   ,101
           ,1
           ,'03/01/2021'
           ,'20:55'
           ,2000
           ,'English')

Thanks a lot for your help

Ok I got my stuff WORKING.

I was able to find an easier to follow example that worked for me.

Here is my solution for anyone who finds himself/herself in my situation.

As stated previously, there are three tables, Speakers, Sermons and ShootLog

Speakers has SpeakerID as primary key

Sermons has SermonID as primary key and SpeakerID as foreign key to Speakers table.

ShootLog has LogID as primary key, SermonID as foreign key to Sermons table and SpeakerID as foreign key to Speakers table.

Our goal is that since Speakers table is the parent table, to delete or update a record in Speakers that also exists only in Sermons table and to avoid data inconsistency, the following will ensure that any record deleted or updated in Speakers table also deletes or updates the associated record in Sermons table:

ALTER TABLE [dbo].[Sermons]  WITH CHECK ADD  CONSTRAINT [FK_Speaker_Sermons] FOREIGN KEY([Sermons],[SpeakerID])
REFERENCES [dbo].[Speakers] ([SpeakerID])
ON UPDATE CASCADE
ON DELETE  CASCADE
GO

Similarly, to delete or update a record in Speakers table that exists in both Sermons table (SpeakerID) and ShootLog table (SermonID), the following will do the trick:

ALTER TABLE [dbo].[ShootLog]  WITH CHECK ADD  CONSTRAINT [FK_ShootLog_Sermons] FOREIGN KEY([SermonID])
REFERENCES [dbo].[Sermons] ([SermonID])
ON UPDATE CASCADE
ON DELETE  CASCADE
GO

This not only ensures that any records deleted or updated in Speakers table also deletes or updates both Sermons table and ShootLog table, it also ensures that any record deleted or updated in Sermons table also deletes or updates associated records in ShootLog table.

Finally, a user can delete only records ShootLog table without deleting any of the other tables.

This works like we intend it to work.

Nice. Or if space requirement is not an issue you can do a soft delete

Soft deletes are a nightmare for the SQL optimizer (soft deletes originated at a time when an entire file had to be read anyway, so the delete flag was, in effect, free). Rather than a soft delete, move the deleted rows to a different table.

1 Like

not a delete flag, useless like you said. but a date range indicating something is alive or dead. archive is also a nice mechanism