Составление SQL запроса на удаление в связанных таблицах

Question

Есть три таблицы: company (компания), department (отдел), subdivision (подотдел). Прошу помочь написать запрос следующего вида: удаляем компанию "Копыта" с id = 2, раз компании такой больше нет, то соответственно удалить отделы содержащие id_company = 2, а у каждого удаляемого отдела, удалить все подотделы.

Я понял как удалять из связанной таблицы, например DELETE FROM Department WHERE id_company = 2, а как мне удалить тогда подотделы (subdivision) удаляемых отделов?

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "Company" (
    "id"    INTEGER,
    "name_company"  TEXT
);
CREATE TABLE IF NOT EXISTS "Department" (
    "id"    INTEGER,
    "id_company"    INTEGER,
    "name department"   TEXT
);
CREATE TABLE IF NOT EXISTS "Subdivision" (
    "id"    INTEGER,
    "id_department" INTEGER,
    "name_sub"  TEXT
);
INSERT INTO "Company" VALUES (1,'Рога');
INSERT INTO "Company" VALUES (2,'Копыта');
INSERT INTO "Company" VALUES (3,'Енот');
INSERT INTO "Department" VALUES (1,2,'Отдел копыт');
INSERT INTO "Department" VALUES (2,1,'Отдел рогов');
INSERT INTO "Department" VALUES (3,3,'Енотный отдел');
INSERT INTO "Subdivision" VALUES (1,1,'Рога и нетолько');
COMMIT;

Answer 1

Если таблицы связаны между собой, то на мой взгляд проще включить каскадное удаление

Answer 2

Для этого используются каскадные удаления по Foreign key:

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "Company" (
    "id"    INTEGER PRIMARY KEY,
    "name_company"  TEXT
);
CREATE TABLE IF NOT EXISTS "Department" (
    "id"    INTEGER PRIMARY KEY,
    "id_company"    INTEGER,
    "name department"   TEXT,
    CONSTRAINT fk_companies
    FOREIGN KEY (id_company)
    REFERENCES Company(id)
    ON DELETE CASCADE

);
CREATE TABLE IF NOT EXISTS "Subdivision" (
    "id"    INTEGER PRIMARY KEY,
    "id_department" INTEGER,
    "name_sub"  TEXT,
     CONSTRAINT fk_departments
    FOREIGN KEY (id_department)
    REFERENCES departments(id)
    ON DELETE CASCADE
);
INSERT INTO "Company" VALUES (1,'Рога');
INSERT INTO "Company" VALUES (2,'Копыта');
INSERT INTO "Company" VALUES (3,'Енот');
INSERT INTO "Department" VALUES (1,2,'Отдел копыт');
INSERT INTO "Department" VALUES (2,1,'Отдел рогов');
INSERT INTO "Department" VALUES (3,3,'Енотный отдел');
INSERT INTO "Subdivision" VALUES (1,1,'Рога и нетолько');
COMMIT;

На выходе имеем каскадное удаление. Код для проверки:

select * from Company c
left join Department d on c.id = d.id_company
left join Subdivision s on s.id_department = d.id

;
delete from Company where id = 2
;
select * from Company c
left join Department d on c.id = d.id_company
left join Subdivision s on s.id_department = d.id

Всё это можно посмотреть здесь: https://www.db-fiddle.com/f/rxrd88bNJL7S1uAhcJ4Up6/1

Answer 3

Если каскадное удаление не установлено, то можно получить Id, которые надо удалить, из подзапроса:

DELETE FROM Subdivision 
WHERE id_department IN (
    SELECT id FROM Department WHERE id_company=2);

Вот в песочнице

БЛОГ НА HUSL

Составление SQL запроса на удаление в связанных таблицах

Ответы (3 шт):