How to use DELETE ON CASCADE in this senario?

Question

so I have 3 tables, Applicant, Vacancy and a Link table. I am trying to have the applicant data removed when the vacancy is removed. Here is the current SQL code I have, would this remove the applicant?

CREATE TABLE Applicant(
  ID INT PRIMARY KEY,
  name varchar(20),
  address varchar(20),
  VacancyID INT,
  FOREIGN KEY (VacancyID) REFERENCES Vacancy(ID) ON DELETE CASCADE);


CREATE TABLE AppVac(
             ApplicantID INT PRIMARY KEY,
             VacancyID INT PRIMARY KEY,
             FOREIGN KEY (ApplicantID) REFERENCES Applicant(ID),
             FOREIGN KEY (VacancyID) REFERENCES Vacancy(ID);

CREATE TABLE Vacancy(
             ID INT PRIMARY KEY,
             .....

I have posted the SQL of creating the three tables. What I am trying to accomplish is when the vacancy is removed the applicant's data is also removed. The applicants are linked to the vancancy via the link table (AppVac) — SmolBrainBoi, May 26 '20 at 12:11

score 0 · Answer 1 · answered May 26 '20 at 11:17

No. The Applicant has not relationship to the Vacancy. What gets removed are all corresponding rows in AppVac.

If you want to require that applicants have at least one row in AppVac, then you'll need a delete trigger on the table. Cascading foreign keys won't do that for you. That is, cascading deletes delete from the referring table, not from the reference table.

How to use DELETE ON CASCADE in this senario?

1 Answers1