FREE AI-Enhanced Online MySQL Compiler - For learning & practice

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE first(
  first_id INTEGER,
  PRIMARY KEY(first_id)
);

CREATE TABLE second(
  first_id INTEGER,
  second_id INTEGER,
  PRIMARY KEY(first_id, second_id),
  FOREIGN KEY (first_id) REFERENCES first(first_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE third(
  first_id INTEGER,
  third_id INTEGER,
  PRIMARY KEY(first_id, third_id),
  FOREIGN KEY (first_id) REFERENCES first(first_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE fourth(
  first_id INTEGER,
  second_id INTEGER,
  third_id INTEGER,
  some_property INTEGER,
  PRIMARY KEY(first_id, second_id, third_id, some_property),
  FOREIGN KEY(first_id, second_id) REFERENCES second(first_id, second_id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY(first_id, third_id) REFERENCES third(first_id, third_id) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO first(first_id) VALUES (1);
INSERT INTO second(first_id, second_id) VALUES(1,1);
INSERT INTO third(first_id, third_id) VALUES(1,1);
INSERT INTO fourth(first_id, second_id, third_id, some_property) VALUES(1,1,1,1);

UPDATE first SET first_id=first_id - 1;

SELECT * FROM first;
SELECT * FROM second;
SELECT * FROM third;
SELECT * FROM fourth;

ERROR 1452 (23000) at line 35: Cannot add or update a child row: a foreign key constraint fails (`sandbox_db`.`fourth`, CONSTRAINT `fourth_ibfk_2` FOREIGN KEY (`first_id`, `third_id`) REFERENCES `third` (`first_id`, `third_id`) ON DELETE CASCADE ON UPDATE CASCADE)