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
