MySQL外键“ON DELETE CASCADE”跨3桌 [英] MySQL Foreign Key "ON DELETE CASCADE" across 3 tables

问题描述

我的DB中有3个表格（还有更多，但是这些表格还没有连接）
$ b $ ul

表格分子带有列id和molecule_id以及引用molecule.id的外键的列id

表描述符

表 tDepDescriptor与列id和descriptor_id和一个外键引用descriptor.id


每个表有更多的列，但没有一个充当外键或类似的东西）

所有外键都指定了on delete cascade，所有的id都是unsigned int（5）。

现在，如果我试图删除descriptor和tDepDescriptor中引用条目的molecule中的条目，那么就不会有外键设置为更新限制，不会给出错误。
如果我删除描述符中的条目，tDepDescriptor中的所有引用条目都将被删除。
如果我试图删除描述符中引用条目的分子中的一个条目，但是没有引用这些描述符条目到tDepDescriptor中的条目，则会发生同样的情况。
所以删除级联适用于两个表，但是当涉及三个表时，级联似乎不会被传递。

表格应该做的是：
当我想删除分子中的一个条目时，描述符中的所有引用条目都被删除。因此，tDepDescriptor中所有引用descriptor中删除条目的条目也被删除。

mysql服务器版本是5.1，引擎是InnoDB

希望有人能够遵循这个复杂的解释并且能够帮助我。

//编辑：
发现问题。
似乎是phpMyAdmin的问题，而不是数据库。点击PMA中的删除不起作用，但是手动编码查询确实在所有三个表格中级联。奇怪，但至少我知道我的桌子工作正常。

解决方案

ON DELETE CASCADE选项就足够了。看看这个例子：

创建和填充表格：

pre $ code > CREATE TABLE分子（
id INT（11）NOT NULL，
PRIMARY KEY（id）
）
ENGINE = INNODB;

CREATE TABLE描述符（
id INT（11）NOT NULL，
molecule_id INT（11）DEFAULT NULL，
PRIMARY KEY（id），
CONSTRAINT FK_descriptor_molecule_id FOREIGN KEY（molecule_id）
REFERENCES分子（ID）ON DELETE CASCADE ON UPDATE RESTRICT
）
ENGINE = INNODB;

CREATE TABLE tdepdescriptor（
id INT（11）NOT NULL，
descriptor_id INT（11）DEFAULT NULL，
PRIMARY KEY（id），
CONSTRAINT FK_tdepdescriptor_descriptor_id FOREIGN KEY（descriptor_id）
REFERENCES描述符（id）ON DELETE CASCADE ON UPDATE RESTRICT
）
ENGINE = INNODB;

INSERT INTO分子值
（1），
（2），
（3）;

INSERT INTO描述符值
（1,1），
（2，1），
（3，2）;

INSERT INTO tdepdescriptor VALUES
（1,1），
（2,2），
（3,3）;

删除一个分子及其所有描述符及其所有tdepdescriptor：

  DELETE FROM molecule WHERE id = 1; 
 
 SELECT * FROM分子; 
 + ---- + 
 | id | 
 + ---- + 
 | 2 | 
 | 3 | 
 + ---- + 
 
 SELECT * FROM descriptor; 
 + ---- + ------------- + 
 | id |分子_id | 
 + ---- + ------------- + 
 | 3 | 2 | 
 + ---- + ------------- + 
 
 SELECT * FROM tdepdescriptor; 
 + ---- + --------------- + 
 | id | descriptor_id | 
 + ---- + --------------- + 
 | 3 | 3 | 
 + ---- + --------------- + 
  

I have 3 tables in my DB (there are more, but there are no connections to these yet)

• table "molecule" with column id
• table "descriptor" with columns "id" and "molecule_id" and a foreign key referencing "molecule.id"
• table "tDepDescriptor" with columns "id" and "descriptor_id" and a foreign key referencing "descriptor.id "

(each table has more columns, but none of these act as foreign keys or anything like that)

All foreign keys have "on delete cascade" specified, all ids are unsigned int(5).

Now, if I try to delete an entry in "molecule" for which there are referencing entries in "descriptor" and "tDepDescriptor" nothing happens as if the foreign keys were set to "on update restrict", no error is given. If I delete an entry in "descriptor", all referencing entries in "tDepDescriptor" are deleted like they should. The same happens if I try to delete an entry in "molecule" for which there are referencing entries in "descriptor", but no referencing entries to those "descriptor"-entries in "tDepDescriptor". So "on delete cascade" works for two tables, but the "cascade" does not seem to be passed on when three tables are involved.

What the tables are supposed to do is: When I want to delete an entry in "molecule", all referencing entries in "descriptor" are deleted. And therefore all entries in "tDepDescriptor" that have a reference to one of the deleted entries in "descriptor" are also deleted.

mysql server version is 5.1, engine is InnoDB

Is hope someone could follow this complicated explanation and can help me.

//EDIT: Found the problem. Seems to be a problem with phpMyAdmin, not with the database. clicking on delete in PMA did not work, but coding the query by hand did, cascading through all three tables. Strange, but at least I know my tables work correctly.

解决方案

It is enough to have ON DELETE CASCADE option. Have a look at this example:

Create and fill tables:

CREATE TABLE molecule (
  id INT(11) NOT NULL,
  PRIMARY KEY (id)
)
ENGINE = INNODB;

CREATE TABLE descriptor (
  id INT(11) NOT NULL,
  molecule_id INT(11) DEFAULT NULL,
  PRIMARY KEY (id),
  CONSTRAINT FK_descriptor_molecule_id FOREIGN KEY (molecule_id)
    REFERENCES molecule(id) ON DELETE CASCADE ON UPDATE RESTRICT
)
ENGINE = INNODB;

CREATE TABLE tdepdescriptor (
  id INT(11) NOT NULL,
  descriptor_id INT(11) DEFAULT NULL,
  PRIMARY KEY (id),
  CONSTRAINT FK_tdepdescriptor_descriptor_id FOREIGN KEY (descriptor_id)
    REFERENCES descriptor(id) ON DELETE CASCADE ON UPDATE RESTRICT
)
ENGINE = INNODB;

INSERT INTO molecule VALUES 
  (1),
  (2),
  (3);

INSERT INTO descriptor VALUES 
  (1, 1),
  (2, 1),
  (3, 2);

INSERT INTO tdepdescriptor VALUES 
  (1, 1),
  (2, 2),
  (3, 3);

Delete one molecule and all its descriptor and all its tdepdescriptor:

DELETE FROM molecule WHERE id = 1;

SELECT * FROM molecule;
+----+
| id |
+----+
|  2 |
|  3 |
+----+

SELECT * FROM descriptor;
+----+-------------+
| id | molecule_id |
+----+-------------+
|  3 |           2 |
+----+-------------+

SELECT * FROM tdepdescriptor;
+----+---------------+
| id | descriptor_id |
+----+---------------+
|  3 |             3 |
+----+---------------+

这篇关于MySQL外键“ON DELETE CASCADE”跨3桌的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！