mysql - 为什么 ON DELETE CASCADE 不删除引用的记录？

Question

我创建了一个 SQLFiddle使用下面显示的代码。问题是在 DELETE 语句之后，关联的 credit_card 记录也应该被删除。

CREATE TABLE person (
  id BIGINT AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE credit_card (
  id BIGINT AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE person_credit_card (
  person_id BIGINT NOT NULL,
  credit_card_id BIGINT NOT NULL UNIQUE, -- Please note that this is UNIQUE
  PRIMARY KEY(person_id, credit_card_id),

  CONSTRAINT fk__person_credit_card__person
    FOREIGN KEY (person_id)
    REFERENCES person(id), 

  KEY pkey (credit_card_id),
  CONSTRAINT fk__person_credit_card__credit_card
    FOREIGN KEY (credit_card_id)
    REFERENCES credit_card(id) 
      ON DELETE CASCADE 
      ON UPDATE CASCADE
);

INSERT INTO person (id) VALUES (1);
INSERT INTO credit_card (id) VALUES (1);
INSERT INTO person_credit_card (person_id, credit_card_id) VALUES (1, 1);

DELETE FROM person_credit_card WHERE credit_card_id = 1;

我不确定为什么这不起作用。使用 credit_card_id 上的 UNIQUE 约束，这是不可能的:

+--------------------------------------+
| person_credit_card                   |
+--------------------------------------+
| person_id | credit_card_id           |
+--------------------------------------+
| 1         | 1                        |
+--------------------------------------+
| 2         | 1                        |
+--------------------------------------+

那么我在这里做错了什么，我该如何让它发挥作用？

---

我也尝试过，例如删除一个 person 并删除他所有的 credit_card 记录(参见其他 SQLFiddle ):

CREATE TABLE person (
  id BIGINT AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE credit_card (
  id BIGINT AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE person_credit_card (
  person_id BIGINT NOT NULL,
  credit_card_id BIGINT NOT NULL UNIQUE,
  PRIMARY KEY(person_id, credit_card_id),

  CONSTRAINT fk__person_credit_card__person
    FOREIGN KEY (person_id)
    REFERENCES person(id)
      ON DELETE CASCADE 
      ON UPDATE CASCADE, 

  CONSTRAINT fk__person_credit_card__credit_card
    FOREIGN KEY (credit_card_id)
    REFERENCES credit_card(id) 
      ON DELETE CASCADE 
      ON UPDATE CASCADE
);

INSERT INTO person (id) VALUES (1);
INSERT INTO person (id) VALUES (2);
INSERT INTO credit_card (id) VALUES (1);
INSERT INTO credit_card (id) VALUES (2);
INSERT INTO credit_card (id) VALUES (3);

INSERT INTO person_credit_card (person_id, credit_card_id) VALUES (1, 1);
INSERT INTO person_credit_card (person_id, credit_card_id) VALUES (1, 2);
INSERT INTO person_credit_card (person_id, credit_card_id) VALUES (2, 3);

DELETE FROM person WHERE id = 1;

但结果是只有解析表丢失了它的条目，但 credit_card 记录仍然存在。

Answer 1

来自documentation :

CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported.