MySQL - ERROR 1452 (23000) - Cannot add or update a child row: a foreign key constraint fails

[Drenriza Housen]

Hi all!

I have a table as follows



Field Type        Null        Key Default        Extra
-------------------------------------------------------------------------------------------------------------------------
id      int(11) NO PRI NULL auto_increment              
xxxx varchar(4) YES NULL STORED GENERATED            
sent tinyint(4)        NO 0                                              
xxxx varchar(45) NO NULL                                          
date timestamp        NO MUL CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP



Inserting data into the table works as expected, and is done through a AFTER INSERT trigger on another table.

But when ever i try and update sent in the table, through
'UPDATE tableName SET sent = 1 WHERE id = 1;'

I get the following error
'ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails'

The FK (date) exists is inserted correctly in the table, and it matches its parent table record, it is also possing to get records from the parent through an INNER JOIN on the date column no issues.

The table is created through MySQL workbench EER diagram as an export (Forward engineer).

I have tried and Google the issue, but all the posts i can find only refer to that you need to have a matching FK in the parent when inserting into the child, which i have.
I have also tried to only insert one record in parent and child tables, but still the update fails with a single record in each.

I am probably missing something obvious, but i cant see it.
Hope someone can point me in the right direction.

Best regards.

[Martin Vashko]

For starters, could you share the DDL statement for the entire table here? You can get it from some SQL client tools, such as HeidiSQL (which should come with MySQL by default, I think).

It looks like the sent column is part of some foreign key. We need to find out which one it is.

[Drenriza Housen]

Hi Martin! Thanks for your reply.
Well that makes sense

CREATE TABLE IF NOT EXISTS `DB1`.`TB1` (  `id` INT NOT NULL AUTO_INCREMENT,  `xxxx1` VARCHAR(4) GENERATED ALWAYS AS (LEFT(MD5(CONCAT(cMD5, 'random45')),4)) STORED,  `sent` TINYINT NOT NULL DEFAULT 0,  `xxxx2` VARCHAR(45) NOT NULL,  `date1` TIMESTAMP NOT NULL,  PRIMARY KEY (`id`),  INDEX `fk_TB1_TB2-1_idx` (`date1` ASC),  CONSTRAINT `fk_TB1_TB2-1`    FOREIGN KEY (`date1`)    REFERENCES `TB1`.`TB2` (`date2`)    ON DELETE NO ACTION    ON UPDATE NO ACTION) ENGINE = InnoDB;

[Stephan van Hulst]

It's probably because your date field is updated with the current timestamp and no longer refers to a valid row in the other table.

Using timestamps as keys is a bad idea. Doesn't the second table have a proper key column you can refer by?

On an unrelated note, whatever your `xxxx1` column represents, its definition looks very iffy. I'm 80% sure it's not going to do what you want it to do.

[Tim Holloway]

Using timestamps as keys is a very bad idea. Like floating-point numbers, timestamps can be very fuzzy and so it's virtually impossible to reliable compare them for equality. Especially since the granularity of JDBC time objects (such as java.sql.Date) and the actual granularity of the timestamp fields in databases frequently do not agree. Date has a granularity of milliseconds, but the Oracle coarse-grained timestamp is only accurate to seconds, and the fine-grained timestamp is in microseconds, if memory serves.

[Drenriza Housen]

Thanks for the reply @Tim Holloway and @Stephan van Hulst !
You were spot on, the issue was the TIMESTAMP value, i created a 'real' PK and the issue was solved.
Also thanks for letting me know it was 'bad practice' so i could read up on it.