Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Junilu Lacar
  • Martin Vashko
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Scott Selikoff
  • salvin francis
  • Piet Souris

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

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Marshal
Posts: 3838
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Martin! Thanks for your reply.
Well that makes sense

 
Saloon Keeper
Posts: 10875
235
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Saloon Keeper
Posts: 21312
140
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
permaculture is a more symbiotic relationship with nature so I can be even lazier. Read tiny ad:
Sauce Labs - World's Largest Continuous Testing Cloud for Websites and Mobile Apps
https://coderanch.com/t/722574/Sauce-Labs-World-Largest-Continuous
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!