Bug #119623 Cannot add or update a child row: a foreign key constraint fails

• Submitted: 5 Jan 12:06
• Modified: 5 Jan 12:35
• Reporter: han wang
• Status: Open
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.0.43
• OS: CentOS
• CPU Architecture: x86

[5 Jan 12:06] han wang

Description:
1. Create a table with DESC UNIQUE KEY;
2. Add a foreign key to the c4 field of the table;
3. Create another index for the c4 field.
At this point, when inserting data into C4, an error "Cannot add or update a child row: a foreign key constraint fails" will be returned.

However, if you directly create a table with the equivalent structure of steps 1, 2, and 3, it will not cause the insertion to fail.

How to repeat:
testcase 1:
CREATE TABLE `t5` (
  `c4` float,
  UNIQUE KEY `i7` (`c4` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE t5 ADD FOREIGN KEY (c4) REFERENCES t5(c4);
CREATE INDEX i8 ON t5(c4);
INSERT INTO t5 (c4) VALUES (1);

result:
mysql> INSERT INTO t5 (c4) VALUES (1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`t5`, CONSTRAINT `t5_ibfk_1` FOREIGN KEY (`c4`) REFERENCES `t5` (`c4`))

testcase 2:
mysql> INSERT INTO t5 (c4) VALUES (1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`t5`, CONSTRAINT `t5_ibfk_1` FOREIGN KEY (`c4`) REFERENCES `t5` (`c4`))

result:
mysql> INSERT INTO t5 (c4) VALUES (1);
Query OK, 1 row affected (0.02 sec)

[5 Jan 12:08] han wang

Correct testcase:

testcase 1:
CREATE TABLE `t5` (
  `c4` float,
  UNIQUE KEY `i7` (`c4` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE t5 ADD FOREIGN KEY (c4) REFERENCES t5(c4);
CREATE INDEX i8 ON t5(c4);
INSERT INTO t5 (c4) VALUES (1);

result:
mysql> INSERT INTO t5 (c4) VALUES (1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`t5`, CONSTRAINT `t5_ibfk_1` FOREIGN KEY (`c4`) REFERENCES `t5` (`c4`))

testcase 2:
CREATE TABLE `t5` (
  `c4` float DEFAULT NULL,
  UNIQUE KEY `i7` (`c4` DESC),
  KEY `i8` (`c4`),
  CONSTRAINT `t5_ibfk_1` FOREIGN KEY (`c4`) REFERENCES `t5` (`c4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO t5 (c4) VALUES (1);

result:
mysql> INSERT INTO t5 (c4) VALUES (1);
Query OK, 1 row affected (0.02 sec)

[5 Jan 12:35] han wang

Further testing revealed that using the copy algorithm to execute ALTER TABLE does not cause insert failures.

CREATE TABLE `t5` (
  `c4` float,
  UNIQUE KEY `i7` (`c4` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE t5 ADD FOREIGN KEY (c4) REFERENCES t5(c4),algorithm=copy;
ALTER TABLE t5 ADD INDEX i8(c4),algorithm=copy;
INSERT INTO t5 (c4) VALUES (1);