CODEWITHSUNDEEP
mysql
user2309351
user2309351

Reputation: 47

Cascade on Delete on Nested Tables

I have tables something like

CREATE TABLE A (
 Z INT NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (Z)
)  ENGINE=InnoDB;

CREATE TABLE B (
Y INT NOT NULL,
Z INT NOT NULL,
PRIMARY KEY (Y , Z),
FOREIGN KEY (Z)
    REFERENCES A (Z)
    ON UPDATE CASCADE ON DELETE CASCADE
)  ENGINE=InnoDB;

CREATE TABLE C (
Y INT NOT NULL,
Z INT NOT NULL,
PRIMARY KEY (Y , Z),
FOREIGN KEY (Y)
    REFERENCES B (Y)
    ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (Z)
    REFERENCES B (Z)
    ON UPDATE CASCADE ON DELETE CASCADE
)  ENGINE=InnoDB;

INSERT INTO A VALUES (1);
INSERT INTO A VALUES (2);
INSERT INTO A VALUES (3);
INSERT INTO B VALUES (4, 2);
INSERT INTO B VALUES (3, 1);
INSERT INTO B VALUES (3, 3);
INSERT INTO B VALUES (4, 3);
INSERT INTO C VALUES (3, 1);
INSERT INTO C VALUES (4, 2);

If I do 

  DELETE FROM A WHERE Z=3

B updates correctly and C doesn't.

If I do 

  DELETE FROM B WHERE Z=3

B updates correctly and C doesn't.

C on cascade is dropping rows completely unrelated to anything. In fact neither of those delete commands should even get to C.

Why is it nuking the C table? Either of the deletes should delete (3,3) and (4,3) in table B and stop.

Upvotes: 0

Views: 362

Answers (1)

user2309351
user2309351

Reputation: 47

The issue was that the I was deleting things as if they were distinct and not part of a composite key. So I would have to have in Table C something along the lines of

  CREATE TABLE C (
  Y INT NOT NULL,
  Z INT NOT NULL,
  PRIMARY KEY (Y , Z),
  FOREIGN KEY (Y , Z)
      REFERENCES B (Y , Z)
      ON UPDATE CASCADE ON DELETE CASCADE
  )  ENGINE=InnoDB;

Which then takes into account that it's a composite key it works correctly.

Upvotes: 1

Related Questions