主页 / 问题 / 1329889
Accepted
Konstantin
Asked:2022-09-19 02:41:00 +0800 CST2022-09-19 02:41:00 +0800 CST

在相关表中编译 SQL 删除查询

  • 772

共有三张表：company（公司）、department（部门）、subdivision（细分）。请帮我写一个这样的查询：删除id = 2的公司“Hooves”，因为没有这样的公司，然后分别删除id_company = 2的部门，对于每个要删除的部门，删除所有子部门。

在此处输入图像描述

例如，我想出了如何从链接表中删除DELETE FROM Department WHERE id_company = 2，但是如何删除要删除的部门的细分（细分）？

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;
sql

3 个回答

  1. Best Answer

    为此，使用外键级联删除：

    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

    • 2

  2. 如果未设置级联删除，则可以从子查询中获取要删除的 Id：

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

    在沙箱中

    • 1

  3. 如果表是互连的，那么我认为启用级联删除更容易在此处输入图像描述

    • 0

相关问题