-- Server version: 10.2.12-MariaDB FreeBSD Ports

-- setup DB structure

SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE member (

  id int(11) NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (id)

) ENGINE=InnoDB;

INSERT INTO member VALUES (1);

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;

-- show inserted data

SELECT * FROM member;

+----+

| id |

+----+

|  1 |

+----+

SELECT * FROM address;

+----+-----------+

| id | member_id |

+----+-----------+

|  2 |         1 |

+----+-----------+

SELECT * FROM payment_method;

+----+-----------+-----------------------+

| id | member_id | cardholder_address_id |

+----+-----------+-----------------------+

|  3 |         1 |                     2 |

+----+-----------+-----------------------+

-- finished setup now delete the central `member` record 

DELETE FROM member WHERE id = 1;

-- check the resulting state

SELECT * FROM member;

-- empty set, correct

SELECT * FROM address;

-- empty set, correct

SELECT * FROM payment_method;

+----+-----------+-----------------------+

| id | member_id | cardholder_address_id |

+----+-----------+-----------------------+

|  3 |         1 |                  NULL |

+----+-----------+-----------------------+

-- should be an empty set

-- referential integrity is broken: payment_method.member_id references a non-existent member.id

-- inconsistency continues during subsequent left join queries

SELECT

  payment_method.id, member_id

FROM

  payment_method

LEFT JOIN

  member ON member.id=payment_method.member_id

WHERE

  payment_method.member_id IS NOT NULL AND member.id IS NULL;

--  empty set, when it should not be

-- note that if you dump and insert the broken DB, then above left join DOES show the broken FK, which is how I discovered it