SQL: Complex Deletion help

Question

I basically created some tables to play around with: I have Two main tables, and a Many-Many join table. Here is the DDL: (I am using HSQLDB)

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

CREATE TABLE JOB
(
    JOB_ID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 
    NAME VARCHAR(50)
)
CREATE TABLE JOB_PERSON
(
    PERSON_ID INTEGER, 
    JOB_ID INTEGER
)
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

ALTER TABLE PERSON ADD
    CONSTRAINT FK_PERSON_PERSON FOREIGN KEY(MAIN_PERSON_ID) 
    REFERENCES PERSON ON DELETE CASCADE ON UPDATE CASCADE

insert into person values(null,'Arthur', null);
insert into person values(null,'James',0);
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);

I want to create a delete statement that deletes orphans from JOB (if there exists only one entry in the join table for a specific job) based on the PERSON.PERSON_ID.

In pseudo language:

delete from job where job_person.job_id=job.job_id 
AND count(job_person.job_id)=1 AND job_person.person_id=X

Where X is some person_id. I have tried a lot of different ways; I think it is the "COUNT" part that is causing problems. I am an SQL rookie, so any help would be much appreciated.

Answer 1

I'm not following.

You cannot delete JOB rows which have JOB_PERSON rows (even one) because of your FK contraints. Thus there is no way to delete JOB rows based on PERSON rows.

JOB_PERSON rows have to be deleted before either a JOB or PERSON can be deleted.

If you want to delete all JOB rows with no JOB_PERSON, then one way is:

DELETE FROM JOB
WHERE JOB_ID NOT IN (
    SELECT JOB_ID
    FROM JOB_PERSON
)

If you want to delete all JOB_PERSON rows for a particular person and then all orphans, do it in two steps:

DELETE FROM JOB_PERSON
WHERE PERSON_ID = X

DELETE FROM JOB
WHERE JOB_ID NOT IN (
    SELECT JOB_ID
    FROM JOB_PERSON
)

If you want to delete only the orphan JOBs previously linked to X, you will need to hold those in a temporary table before the first delete.

INSERT INTO TEMP_TABLE
SELECT JOB.JOB_ID
FROM JOB
INNER JOIN JOB_PERSON
    ON JOB_PERSON.JOB_ID = JOB.JOB_ID
WHERE JOB_PERSON.PERSON_ID = X

DELETE FROM PERSON
WHERE PERSON_ID = X

-- YOUR CASCADING DELETE DOES THIS:
/*
DELETE FROM JOB_PERSON
WHERE PERSON_ID = X
*/

-- Now clean up (only) new orphans on the other side
DELETE FROM JOB
WHERE JOB_ID NOT IN (
    SELECT JOB_ID
    FROM JOB_PERSON
)
AND JOB_ID IN (
    SELECT JOB_ID
    FROM TEMP_TABLE
)

Answer 2

This will delete from your table JOB entries which have no entry in the table JOB_PERSON (Orpheans).

DELETE FROM JOB
WHERE JOB_ID NOT IN (
    SELECT JOB_ID
    FROM JOB_PERSON
)

You can't delete rows which are linked by a foreign on an other table...