如何从表sql中删除行

I want to delete specific rows from 8 tables. My problem is that the rows are connected with foreign key. How can I delete all the data that connected to the specific rows that I want to delete? My tables include definition tables (like id, name ,max value, min value...), data tables (like id, user_id, definition_id,....) and history tables (save every change in data table).

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

I thought to use delete on cascade command but I could not find a way to use it.

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

2 个解决方案

#1

DELETE CASCADE is an attribute of the foreign key constraint. Unfortunately it's not something you can use as an option with a DELETE statement (which would be really cool actually)

删除级联是外键约束的一个属性。不幸的是，你不能把它作为删除语句的选项(这真的很酷)

If your foreign keys have not been declared as cascading you need to "work your way up".

如果您的外键没有被声明为级联，那么您需要“以自己的方式向上”。

Unfortunately you did not show us your real table structure so let's assume something like this:

不幸的是，您没有向我们展示真正的表结构，所以让我们假设如下:

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")

(我知道你说了8张表，但为了演示，我把它缩短了一点，但这并没有改变根本的“策略”)

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

假设要从' main_table中删除main_id = 42的行:

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

首先需要从child_three中删除行，如下所示:

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

Then delete the rows from child_two:

然后从child_two中删除行:

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

Then child_one:

然后child_one:

delete from child_one
where main_id = 42;

And finally the main table:

最后是主表:

delete from main_table
where id = 42;

Some SQL clients can actually generate those statements for you. I don't know if SQL Developer can though.

一些SQL客户机实际上可以为您生成这些语句。我不知道SQL开发人员能否做到。

#2

I assume that you use InnoDB Engine, since you are talking about foreign keys,

假设你用的是InnoDB引擎，既然你说的是外键，

Easier will be to define properly the table so that a deletion will act as a cascade deletion.

更容易的方法是正确地定义表，以便删除将作为级联删除。

     CONSTRAINT `myForeignKey` FOREIGN KEY (`typeId`)
        REFERENCES `types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

Here is alink with a proper create table statement:

下面是alink提供了正确的create table语句:

How do I use on delete cascade in mysql?

如何在mysql中使用删除级联?

#1