MySQL删除级联.测试例 [英] MySQL on delete cascade. Test Example

问题描述

我想知道这个测试问题.我自己准备了示例并进行了测试，但我仍然不确定答案.

I am wondering about this test question. I prepared the example myself and tested it but I still feel unsure of the answer.

具有以下内容:

CREATE TABLE foo (
  id INT PRIMARY KEY AUTO_INCREMENT, 
  name INT
)

CREATE TABLE foo2 (
  id INT PRIMARY KEY AUTO_INCREMENT, 
  foo_id INT REFERENCES foo(id) ON DELETE CASCADE
)

据我所知，答案是:

a.创建了两个表

尽管也有:

b.如果删除表foo2中foo_id为2的行，则表foo中id = 2的行将被自动删除

d.如果删除表foo中id = 2的行，则表foo2中所有foo_id = 2的行都将被删除

在我的示例中，我将使用delete语法:

In my example I would have used the delete syntax:

DELETE FROM foo WHERE id = 2;
DELETE FROM foo2 WHERE foo_id = 2;

由于某种原因，我似乎找不到表之间的任何关系，尽管看起来应该有一个关系.也许有一些MySQL设置，或者在表创建查询中未正确使用ON DELETE CASCADE?我只是想知道...

For some reason I was unable to find any relationship between the tables although it seems like there should be one. Maybe there is some MySQL setting or perhaps is ON DELETE CASCADE not used properly in the table creation queries? I am left wondering...

推荐答案

答案d.并且仅当存储引擎实际支持并强制执行外键约束时，该命令才是正确的.

Answer d. is correct, if and only if the storage engine actually supports and enforces foreign key constraints.

如果用Engine=MyISAM创建表，则b都不行.或d.是正确的.

If the tables are created with Engine=MyISAM, then neither b. or d. is correct.

如果使用Engine=InnoDB创建表，则 d.是正确的.

If the tables are created with Engine=InnoDB, then d. is correct.

注意:

当且仅当FOREIGN_KEY_CHECKS = 1时，这对于InnoDB是正确的；如果为FOREIGN_KEY_CHECKS = 0，则来自父表(foo)的DELETE将不从子表(foo2)中删除引用从父表中删除的行的行.

This is true for InnoDB if and only if FOREIGN_KEY_CHECKS = 1; if FOREIGN_KEY_CHECKS = 0, then a DELETE from the parent table (foo) will not remove rows from the child table (foo2) that reference a row removed from the parent table.

使用SHOW VARIABLES LIKE 'foreign_key_checks'的输出进行验证(1 = ON，0 = OFF) (通常的默认设置是开".)

Verify this with the output from SHOW VARIABLES LIKE 'foreign_key_checks' (1=ON, 0=OFF) (The normal default is for this to be ON.)

SHOW CREATE TABLE foo的输出将显示表使用的引擎.

The output from SHOW CREATE TABLE foo will show which engine the table uses.

SHOW VARIABLES LIKE 'storage_engine'的输出将显示创建表且未指定引擎时使用的默认引擎.

The output from SHOW VARIABLES LIKE 'storage_engine' will show the default engine used when a table is created and the engine is not specified.

这篇关于MySQL删除级联.测试例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！