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

I have a database of components. Each component is of a specific type. That means there is a many-to-one relationship between a component and a type. When I delete a type, I would like to delete all the components which has a foreign key of that type. But if I'm not mistaken, cascade delete will delete the type when the component is deleted. Is there any way to do what I described?

我有一个组件数据库。每个组件都是特定类型的。这意味着组件和类型之间存在多对一的关系。当我删除一个类型时，我想删除所有具有该类型的外键的组件。但是如果我没有弄错，那么在删除组件时，cascade delete将删除类型。有没有办法做到我所描述的?

3 个解决方案

#1

Here's what you'd include in your components table.

以下是您在组件表中包含的内容。

CREATE TABLE `components` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `typeId` int(10) unsigned NOT NULL,
    `moreInfo` VARCHAR(32), 
    -- etc
    PRIMARY KEY (`id`),
    KEY `type` (`typeId`)
    CONSTRAINT `myForeignKey` FOREIGN KEY (`typeId`)
      REFERENCES `types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

Just remember that you need to use the InnoDB storage engine: the default MyISAM storage engine doesn't support foreign keys.

只需记住，您需要使用InnoDB存储引擎:默认的MyISAM存储引擎不支持外键。

#2

use this sql

使用这个sql

DELETE T1, T2 FROM T1 INNER JOIN T2 ON T1.key = T2.key WHERE condition

从T1的内部连接T2中删除T1, T2。关键= T2。关键的地方条件

#3

You have to define your Foreign Key constraints as ON DELETE CASCADE.

您必须将外键约束定义为删除级联。

Note: You need to use InnoDB storage engine, he default MyISAM storage engine not support foreign keys relation.

注意:您需要使用InnoDB存储引擎，他默认为MyISAM存储引擎不支持外键关系。

CREATE TABLE `table2` (
`id` int(11) NOT NULL auto_increment,
`name` int(11) NOT NULL,

PRIMARY KEY (`id`),
KEY `ids` (`ids`)
CONSTRAINT `foreign` FOREIGN KEY (`ids`)
  REFERENCES `table2` (`ids`) ON DELETE CASCADE ON UPDATE CASCADE
)

#1