[Maria-developers] Triangular FKs - Cascade delete leaves DB in state with broken referential integrity

3 Feb 2018

      Initially posted on "discuss" list...

I have come across a very strange symptom with triangular FKs (see
below) where a cascade delete of the central (member) record leaves the
DB in a state with broken referential integrity.

The FK structure is a little bit demanding and somewhat
"triangular/circular", but has been in operation in our application for
many years without problems. After recent upgrade from 10.1 to 10.2.12,
we promptly got broken FKs. (I have not yet retested with 10.1).

How to reproduce: (example reduced to the bare essentials with 3 tables
with 1 record each).

SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE member (
  id int(11) NOT NULL AUTO_INCREMENT,
  default_address_id int(11) DEFAULT NULL,
  default_card_id int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY member_FI_2 (default_address_id),
  KEY member_FI_3 (default_card_id),
  CONSTRAINT member_FK_2 FOREIGN KEY (default_address_id) REFERENCES
address (id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT member_FK_3 FOREIGN KEY (default_card_id) REFERENCES
payment_method (id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;

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

CREATE TABLE address (
  id int(11) NOT NULL AUTO_INCREMENT,
  member_id int(11) NOT NULL,
  PRIMARY KEY (id),
  KEY address_FI_1 (member_id),
  CONSTRAINT address_FK_1 FOREIGN KEY (member_id) REFERENCES member (id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

INSERT INTO address VALUES (2,1);

CREATE TABLE payment_method (
  id int(11) NOT NULL AUTO_INCREMENT,
  member_id int(11) NOT NULL,
  cardholder_address_id int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY payment_method_FI_1 (member_id),
  KEY payment_method_FI_2 (cardholder_address_id),
  CONSTRAINT payment_method_FK_1 FOREIGN KEY (member_id) REFERENCES
member (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT payment_method_FK_2 FOREIGN KEY (cardholder_address_id)
REFERENCES address (id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;

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

SET FOREIGN_KEY_CHECKS=1;

SELECT * FROM member;
+----+--------------------+-----------------+
| id | default_address_id | default_card_id |
+----+--------------------+-----------------+
|  1 |                  2 |               3 |
+----+--------------------+-----------------+

SELECT * FROM address;
+----+-----------+
| id | member_id |
+----+-----------+
|  2 |         1 |
+----+-----------+

SELECT * FROM payment_method;
+----+-----------+-----------------------+
| id | member_id | cardholder_address_id |
+----+-----------+-----------------------+
|  3 |         1 |                     2 |
+----+-----------+-----------------------+

DELETE FROM member WHERE id = 1;

SELECT * FROM member;
[ empty ]

SELECT * FROM address;
[ empty ]

SELECT * FROM payment_method;
+----+-----------+-----------------------+
| id | member_id | cardholder_address_id |
+----+-----------+-----------------------+
|  3 |         1 |                  NULL |
+----+-----------+-----------------------+

------------

DB is now in an inconsistent state: payment_method.member_id=1
references a non-existent member record.

Am I missing something, or should this never happen?

Thanks

-- 
Oliver Schönrock

[Maria-developers] Triangular FKs - Cascade delete leaves DB in state with broken referential integrity

Oliver Schonrock