Why is ON DELETE CASCADE not removing referenced record?

Question

I've created a SQLFiddle with the code shown below. The problem is that after the DELETE statement the associated credit_card record is supposed to be deleted as well.

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;

I'm not sure why this is not working. With the UNIQUE constraint on the credit_card_id this is not possible:

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

So what am I doing wrong here and how can I make it work?

I also tried to e.g. delete a person and remove all his credit_card records (see this other 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;

but the outcome is that only the resolution table is losing its entries but the credit_card records are still there.

Deleting from parent table would reflect in child table not the vice versa — 1000111, Aug 02 '16 at 15:31
Voting to close as a Typo (unlikely to be of use to future readers) — Drew, Aug 02 '16 at 15:34
Representing composition for n-n relationships requires triggers, or similar postprocess technique. — Sebas, Aug 02 '16 at 15:37
composition of n-n can take place in FK's when done right @Sebas — Drew, Aug 02 '16 at 15:39
@Sebas You mean if I don't want to have "ghost" `credit_card` records in my database I'm safer if I writer a trigger function that cleans up for me if I delete e.g. a `person`? — Stefan Falk, Aug 02 '16 at 15:40
Here the parent tables are `credit_card` and `person`. Child table : `person_credit_card`. You are deleting entries from one of the parent tables. So, it gets reflected in the child table. The other parent table should remain unaffected. And this just happened in your case (usual) — 1000111, Aug 02 '16 at 15:40
I wrote is a composition done with FK's [Here](http://stackoverflow.com/a/38340726). As it took half an hour to wrap my head around it, I merely plop the link to read if interested — Drew, Aug 02 '16 at 15:41
@Drew I don't understand this. There's no `CASCADE` or anything and I don't understand what you mean by "*done with FK's*". What is done with FK's? — Stefan Falk, Aug 02 '16 at 15:48
I said `composition of n-n can take place in FK's when done right @ Sebas` ... you said `how is it done right` ... I showed the only Q&A on the stack marked with mysql foreign-keys and composition. How someone is "going to understand" that answer in under an hour is beyond me. I don't write answers that a casual passer-by is typically going to understand. If they are that easy, it generally doesn't interest me. There are dozens of people here willing to answer those. — Drew, Aug 02 '16 at 15:50
@displayname, let me ask you this: if you represent this relationship with an n to n cardinality, why would you automatically remove the credit card? I'm 100% positive that either the n-n is a mistake and you want to do a 1-n instead, or you actually mean to "delete the credit card, if and only if there's no other owner". The first case is easy to solve, the second one requires applicative logic. — Sebas, Aug 02 '16 at 15:51

score 1 · Accepted Answer · answered Aug 02 '16 at 15:33

1

From the 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.

answered Aug 02 '16 at 15:33

Willem Renzema

5,177
1
17
24

score 1 · Answer 2 · answered Aug 02 '16 at 15:53

Deleting from person_credit_card won't cascade person nor credit_card. Cascade works by deleting/updating records from tables that reference the record being deleted. In other words, since person doesn't have a column with reference to person_credit_card, then it won't be deleted.

Why is ON DELETE CASCADE not removing referenced record?

2 Answers2