CREATE TABLE `a` (
  `id` INT UNSIGNED PRIMARY KEY
) ENGINE = INNODB;

CREATE TABLE `b` (
  `id` INT UNSIGNED PRIMARY KEY,
  `a_id` INT UNSIGNED NULL,
  CONSTRAINT FOREIGN KEY `a_b_fk` (`a_id`) REFERENCES `a` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE = INNODB;

CREATE TABLE `c` (
  `id` INT UNSIGNED PRIMARY KEY,
  `a_id` INT UNSIGNED NULL,
  `b_id` INT UNSIGNED NULL,
  CONSTRAINT FOREIGN KEY `a_c_fk` (`a_id`) REFERENCES `a` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT FOREIGN KEY `b_c_fk` (`b_id`) REFERENCES `b` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE = INNODB;

INSERT INTO `a` VALUES (1);
INSERT INTO `b` VALUES (1,1);
INSERT INTO `c` VALUES (1,1,1);

SELECT * FROM `a`;
SELECT * FROM `b`;
SELECT * FROM `c`;

-- will be rejected
DELETE FROM `b` WHERE `id` = 1;
SELECT * FROM `a`;
SELECT * FROM `b`;
SELECT * FROM `c`;

-- will be rejected
-- but could be allowed by using breadth-first instead of depth-first algorithm
DELETE FROM `a` WHERE `id` = 1;
SELECT * FROM `a`;
SELECT * FROM `b`;
SELECT * FROM `c`;
