mysql - 如何从表sql中删除行

Question

我想从 8 个表中删除特定行。我的问题是行与外键相连。如何删除连接到我要删除的特定行的所有数据？我的表包括定义表(如 id、名称、最大值、最小值...)，数据表(如 id、user_id、definition_id、....)和历史表(保存数据表中的每个更改)。

我想在级联命令上使用 delete，但我找不到使用它的方法。

Answer 1

DELETE CASCADE 是外键约束的一个属性。不幸的是，您不能将它用作 DELETE 语句的选项(实际上这真的很酷)

如果您的外键没有被声明为级联的，您需要“逐步提高”。

很遗憾，您没有向我们展示您的真实表结构，所以让我们假设如下:

main_table (main_id)   child_one (id, main_id)     child_two (id, id_one)       child_three (id, id_two)

(I know you said 8 tables, but for the sake of the demonstration I shortened it a bit, but that doesn't change the underlying "strategy")

Assuming you want to delete the row with main_id = 42 from `main_table:

You first need to delete the rows from child_three using something like this:

delete from child_three
where id_two in (select id 
                 from child_two 
                 where id_one in (select id
                                  from child_one 
                                  where main_id = 42);

然后从 child_two 中删除行:

delete from child_two
where id_one in (select id
                 from child_one 
                 where main_id = 42);

然后是 child_one:

delete from child_one
where main_id = 42;

最后是主表:

delete from main_table
where id = 42;

某些 SQL 客户端实际上可以为您生成这些语句。不过，我不知道 SQL Developer 是否可以。