Description:
Hello,
I have some InnoDB tables created with FOREIGN KEY ... ON DELETE CASCADE ON UPDATE CASCADE.
Deleting a row in parent table causes the loss of extra lines in child tables.
See example in "How to repeat".
Checked using mysql 5.1.66 and 5.5.34.
How to repeat:
I have 3 tables with foreign keys:
CREATE TABLE t1 (
c1 INT UNSIGNED PRIMARY KEY NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
CREATE TABLE t2 (
c1 INT UNSIGNED NOT NULL,
c2 INT UNSIGNED NOT NULL,
KEY (c2),
FOREIGN KEY (c1) REFERENCES t1(c1) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
CREATE TABLE t3 (
c1 INT UNSIGNED NOT NULL,
c2 INT UNSIGNED NOT NULL,
c3 INT UNSIGNED NOT NULL,
FOREIGN KEY (c1) REFERENCES t1(c1) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (c2) REFERENCES t2(c2) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
Insert some dummy data:
INSERT INTO t1 (c1) VALUES (1),(2);
INSERT INTO t2 (c1, c2) VALUES (1, 10),(2, 10);
INSERT INTO t3 (c1, c2, c3) VALUES (1, 10, 100),(2, 10, 100);
Check the result:
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
mysql> SELECT * FROM t1;
+----+
| c1 |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)
mysql> SELECT * FROM t2;
+----+----+
| c1 | c2 |
+----+----+
| 1 | 10 |
| 2 | 10 |
+----+----+
2 rows in set (0.00 sec)
mysql> SELECT * FROM t3;
+----+----+-----+
| c1 | c2 | c3 |
+----+----+-----+
| 1 | 10 | 100 |
| 2 | 10 | 200 |
+----+----+-----+
2 rows in set (0.00 sec)
Delete the first row from t1:
DELETE FROM t1 WHERE c1=1;
Now I expect to find one row in table t3, but t3 is empty!
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
mysql> SELECT * FROM t1;
+----+
| c1 |
+----+
| 2 |
+----+
1 row in set (0.00 sec)
mysql> SELECT * FROM t2;
+----+----+
| c1 | c2 |
+----+----+
| 2 | 10 |
+----+----+
1 row in set (0.00 sec)
mysql> SELECT * FROM t3;
Empty set (0.00 sec)