SQL Deletion Cascading Help (Specific Question)

Question

I have two tables (renamed/refactored for illustrative purposes) with a Many-To-Many relationship in an HSQL database. I want everything to be wiped out when I delete from one side of a Many-to-Many relationship (without querying the table; this is performance critical)

Here are my main tables:

CREATE TABLE PERSON
(
    PERSON_ID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 
    NAME VARCHAR(50)
)

CREATE TABLE JOB
(
    JOB_ID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 
    NAME VARCHAR(50)
)

Here is my join table:

CREATE TABLE JOB_PERSON
(
    PERSON_ID INTEGER, 
    JOB_ID INTEGER
)

Here are my constraints:

ALTER TABLE JOB_PERSON ADD 
    CONSTRAINT FK_PERSON_JOB FOREIGN KEY(PERSON_ID) 
    REFERENCES PERSON ON DELETE CASCADE ON UPDATE CASCADE 

ALTER TABLE JOB_PERSON ADD
    CONSTRAINT FK_JOB_PERSON FOREIGN KEY(JOB_ID) 
    REFERENCES JOB ON DELETE CASCADE ON UPDATE CASCADE

I basically want to do this: "delete from person where person_id=0" and have it delete everything from PERSON, JOB_PERSON and JOB if the JOB entity will be orphaned (no longer referenced in the many to many table)

Is this possible without querying the database? When I delete, it only deletes from PERSON and JOB_PERSON. As you can probably tell, my sql skills are lacking.

Here is the dummy data I have been playing around with:

insert into person values(null,'Arthur');
insert into person values(null,'James');
insert into job values(null, 'Programmer')
insert into job values(null, 'Manager')
insert into job_person values(0,0);
insert into job_person values(0,1);
insert into job_person values(1,1);

So if I enter both of these statements:

delete from person where person_id=0
delete from person where person_id=1

I would like to have everything in all 3 tables deleted. Possible?

Answer 1

Create a Store Procedure where you pass the ID, then simply DELETE the proper row in the order you need to.

This way your app isn't tied to the exact order, which might change in the future with DB redesigns. You also gain the added bonus that Stored Procs are faster then sending a bunch of queries.

Although if you absolutely wanted a way to delete the dependent tables when you try to delete that entry, you have to use triggers, but the rule for triggers are, avoid them whenever possible, if another solution exists, use it. So ultimately, using a Stored Proc is the best solution.