SQL Table Datastructure, is this wrong? Using CASCADING Delete

Question

Here are the 3 tables I am having problems with:

• Table: Opportunities - Holds various opportunity(job) descriptions

• Table: Opportunities_Applicants - Holds various applicants applying for opportunities. 1 Applicant can only apply for 1 opportunity, however 1 opportunity can have many applicants

• Table: Opportunities_Category - Holds category name and type. 1 Category can relate to many Opportunities.

I am trying to perform a CASCADING Delete when a Opportunity Category is deleted, it will delete corresponding Opportunities and Applicants for those Opportunities.

Is this structure appropriate or should I setup database differently? How should my table relationships be setup in order for the CASCADING Delete to work when a Opportunity Category is deleted?

Should I even be using CASCADING Delete?

create table Opportunities_Category
(   
CategoryID          int identity(1,1)       not null
    constraint PK_CategoryID primary key clustered,         
[Name]              varchar(150)            not null,
[Type]              varchar(100)            not null --Pay, Volunteer, Volunteer Yearly
)

create table Opportunities
(
OpportunityID       int identity(1,1)   not null
    constraint PK_OpportunityID primary key clustered,
CategoryID          int                     not null
    constraint FK_CategoryID foreign key references Opportunities_Category(CategoryID) ON DELETE CASCADE,       
Title               varchar(300)            not null,
PostingDate         datetime                not null,
ClosingDate         datetime                not null,
Duration            varchar(150)            not null, --Part Time, Full Time, Seasonal, Contract
Compensation        varchar(150)            not null, --Hourly, Volunteer, Salary
[Description]       varchar(5000)           not null,
Qualifications      varchar(5000)           not null,
Show                int                     not null
)

create table Opportunities_Applicant
(
ApplicantID             int identity(1,1)   not null
    constraint PK_ApplicantID primary key clustered,
OpportunityID           int                 not null
    constraint FK_OpportunityID foreign key references Opportunities(OpportunityID) ON DELETE CASCADE,
[First]                 varchar(150)        not null,
[Last]                  varchar(150)        not null,
Phone                   varchar(20)         not null,
Cell                    varchar(20)         not null,
EMail                   varchar(200)        not null,
CoverLetterResume       varchar(300)        null,
[Timestamp]             datetime            not null    
)   

Answer 1

It turns out that my tables are setup properly:

Yesterday, i had been trying to do: DELETE FROM Opportunities WHERE CategoryID = @CategoryID. This was only deleting the records from Opportunities and Opportunities_Applicants.

Today, i changed to: DELETE FROM Opportunities_Categoies WHERE CategoryID = @CategoryID and all 3 tables are deleting their corresponding records!

Answer 2

ALTER TABLE [dbo].[Opportunities] WITH CHECK ADD CONSTRAINT [FK_OpportunitiesCategory_Opportunities] FOREIGN KEY([CategoryID]) REFERENCES [dbo].[Opportunities_Category] ([CategoryID]) ON DELETE CASCADE GO

Good Luck...