ON DELETE CASCADE在MySQL中不起作用 [英] ON DELETE CASCADE not working in MySQL

问题描述

我正在使用以下SQL创建名为app_info的表:

I am using the following SQL to create a table named app_info:

CREATE TABLE IF NOT EXISTS `app_info` (
`_id` int(11) NOT NULL AUTO_INCREMENT,
`app_name` varchar(50) DEFAULT NULL,
`app_owner` varchar(50) DEFAULT NULL,
`last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

我正在使用以下SQL创建名为tab_info的表:

I am using the following SQL to create a table named tab_info:

CREATE  TABLE `myDB`.`tab_info` (
`_id` INT NOT NULL AUTO_INCREMENT ,
`app_id` INT NOT NULL ,
`tab_title` VARCHAR(15) NOT NULL ,
PRIMARY KEY (`_id`) ,
UNIQUE INDEX `app_id_UNIQUE` (`app_id` ASC) ,
INDEX `app_tab_key` (`app_id` ASC) ,
CONSTRAINT `app_tab_key`
  FOREIGN KEY (`app_id` )
  REFERENCES `myDB`.`app_info` (`_id` )
  ON DELETE CASCADE
  ON UPDATE CASCADE); 

但是当我从主键表中删除数据时，外键表中的孤立行不会被自动删除.有谁知道可能是什么问题?

But when I delete data from primary key table, the orphaned rows in the foreign key table are not being deleted automatically. Does anyone know what the problem could be?

推荐答案

MyISAM存储引擎不支持外键约束.约束已解析，但被静默忽略.

The MyISAM storage engine doesn't support foreign key constraints. The constraint is parsed but silently ignored.

要解决您的问题，请改用InnoDB引擎(对于两个表).

To fix your problem use the InnoDB engine instead (for both tables).

CREATE TABLE ( ... ) ENGINE = InnoDB ... ;

---

除了删除表并重新创建表之外，您还可以更改存储引擎:

---

Instead of dropping your tables and recreating them you can also change the storage engine:

ALTER TABLE myDB.app_info ENGINE = InnoDB;
ALTER TABLE myDB.tab_info ENGINE = InnoDB;

更改引擎后，您将需要再次添加外键约束.

After changing the engine you will need to add the foreign key constraint again.

这篇关于ON DELETE CASCADE在MySQL中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！