Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15199

Triangular FKs - Cascade delete causes broken referential integrity

    XMLWordPrintable

Details

    Description

      See SQL test case below for detail. See this thread for problem discovery:
      https://lists.launchpad.net/maria-developers/msg11102.html

      For a "triangular" table FK structure (see attached png visual) deleting parent record, should delete both children, but the ON DELETE CASCADE does not delete record in 2nd child table when 2nd child has ON DELETE SET NULL constraint to 1st child

      Referential integrity is broken after parent is deleted. LEFT JOINs do not show broken FK.

      MySQL 5.7.21 and MariaDB 10.1.30 do not exhibit this broken behaviour.

      -- 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
      

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              oschonrock Oliver Schonrock
              Votes:
              1 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.