Description:
(demo also shows a REAL bug. I'll post that one separately.)
When you create some tables and hang in some foreign key constraints with ON UPDATE
CASCADE, it is possible to receive errors that do not seem to make sense but in reality
are originating from one of the cascading tables.
My bad of course (not yours) but it can cause really illogical errors, like the
following:
mysql> update test_table set klantnr = '15970' where domeinnr = '18854';
ERROR 1062: Duplicate entry '4649' for key 1
The error occured somewhere down the cascading christmas tree.
This is really hard to debug, because the foreign key constraints are in de client table
and not in test_table. This means you have to check, trace and rethink all tables in the
database to debug.
Besides that you cannot trust that the given error is really what's going wrong. See the
repeat-code.
How to repeat:
CREATE TABLE test0 (
field1 varchar(8) NOT NULL default '',
PRIMARY KEY (field1),
UNIQUE KEY field1 (field1)
) TYPE=InnoDB;
CREATE TABLE test1 (
field1 varchar(8) NOT NULL default '',
field2 varchar(8) NOT NULL default '',
PRIMARY KEY (field1,field2),
KEY field1 (field1,field2),
KEY field1_2 (field1),
CONSTRAINT `test1_ibfk_1` FOREIGN KEY (`field1`) REFERENCES `test0` (`field1`)
) TYPE=InnoDB;
CREATE TABLE test2 (
field1 varchar(8) NOT NULL default '',
field2 varchar(8) NOT NULL default '',
UNIQUE KEY uniek (field1,field2),
KEY field1 (field1,field2),
KEY field1_2 (field1),
CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`field1`, `field2`) REFERENCES `test1`
(`field1`, `field2`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
CREATE TABLE test3 (
field1 varchar(8) NOT NULL default '',
PRIMARY KEY (field1),
UNIQUE KEY field1 (field1),
CONSTRAINT `test3_ibfk_1` FOREIGN KEY (`field1`) REFERENCES `test2` (`field1`) ON
DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
INSERT INTO test0 VALUES ('old');
INSERT INTO test0 VALUES ('other');
INSERT INTO test1 VALUES ('old','somevalu');
INSERT INTO test1 VALUES ('other','anyvalue');
INSERT INTO test2 VALUES ('old','somevalu');
INSERT INTO test2 VALUES ('other','anyvalue');
INSERT INTO test3 VALUES ('old');
INSERT INTO test3 VALUES ('other');
update test1 set field1 = 'other' where field2 = 'somevalu';
-- ERROR 1062: Duplicate entry 'other-somevalu' for key 1
-- this error REALLY doesn't make sense:
-- there shouldn't have been any problems until table test3
-- instead it says other-somevalu already exists, which doesn't.
Suggested fix:
A possibility to stack-trace the cascades would be cool. Maybe log the update queries?
Or an EXPLAIN UPDATE that discribes all consequences of an update.
Or even just a query that returns de foreign keys pointing TO the table or index name you
enter:
mysql>show constraints to test_table;
or:
mysql>show constraints to test_table (domeinnr, klantnr);
Description: (demo also shows a REAL bug. I'll post that one separately.) When you create some tables and hang in some foreign key constraints with ON UPDATE CASCADE, it is possible to receive errors that do not seem to make sense but in reality are originating from one of the cascading tables. My bad of course (not yours) but it can cause really illogical errors, like the following: mysql> update test_table set klantnr = '15970' where domeinnr = '18854'; ERROR 1062: Duplicate entry '4649' for key 1 The error occured somewhere down the cascading christmas tree. This is really hard to debug, because the foreign key constraints are in de client table and not in test_table. This means you have to check, trace and rethink all tables in the database to debug. Besides that you cannot trust that the given error is really what's going wrong. See the repeat-code. How to repeat: CREATE TABLE test0 ( field1 varchar(8) NOT NULL default '', PRIMARY KEY (field1), UNIQUE KEY field1 (field1) ) TYPE=InnoDB; CREATE TABLE test1 ( field1 varchar(8) NOT NULL default '', field2 varchar(8) NOT NULL default '', PRIMARY KEY (field1,field2), KEY field1 (field1,field2), KEY field1_2 (field1), CONSTRAINT `test1_ibfk_1` FOREIGN KEY (`field1`) REFERENCES `test0` (`field1`) ) TYPE=InnoDB; CREATE TABLE test2 ( field1 varchar(8) NOT NULL default '', field2 varchar(8) NOT NULL default '', UNIQUE KEY uniek (field1,field2), KEY field1 (field1,field2), KEY field1_2 (field1), CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`field1`, `field2`) REFERENCES `test1` (`field1`, `field2`) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB; CREATE TABLE test3 ( field1 varchar(8) NOT NULL default '', PRIMARY KEY (field1), UNIQUE KEY field1 (field1), CONSTRAINT `test3_ibfk_1` FOREIGN KEY (`field1`) REFERENCES `test2` (`field1`) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB; INSERT INTO test0 VALUES ('old'); INSERT INTO test0 VALUES ('other'); INSERT INTO test1 VALUES ('old','somevalu'); INSERT INTO test1 VALUES ('other','anyvalue'); INSERT INTO test2 VALUES ('old','somevalu'); INSERT INTO test2 VALUES ('other','anyvalue'); INSERT INTO test3 VALUES ('old'); INSERT INTO test3 VALUES ('other'); update test1 set field1 = 'other' where field2 = 'somevalu'; -- ERROR 1062: Duplicate entry 'other-somevalu' for key 1 -- this error REALLY doesn't make sense: -- there shouldn't have been any problems until table test3 -- instead it says other-somevalu already exists, which doesn't. Suggested fix: A possibility to stack-trace the cascades would be cool. Maybe log the update queries? Or an EXPLAIN UPDATE that discribes all consequences of an update. Or even just a query that returns de foreign keys pointing TO the table or index name you enter: mysql>show constraints to test_table; or: mysql>show constraints to test_table (domeinnr, klantnr);