?
Solved

#1452 - Cannot add or update a child row: a foreign key constraint fails

Posted on 2013-09-13
9
Medium Priority
?
28,748 Views
Last Modified: 2013-09-13
Whenever I try to create a relationship between one table and another, to create a foreign key, i get the error:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`expenses`.<result 2 when explaining filename '#sql-22f8_203'>, CONSTRAINT `#sql-22f8_203_ibfk_1` FOREIGN KEY (`users_id`) REFERENCES `users` (`users_id`))

Any ideas what this could be?  Googled this with no useful results. :(

Thanks.
0
Comment
Question by:LB1234
  • 5
  • 2
  • 2
9 Comments
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 39490549
Without seeing the structure of the data and the code that is generation the error, it is not likely that we can do much more than speculate.

Cd&
0
 
LVL 1

Author Comment

by:LB1234
ID: 39490565
Error
SQL query:

ALTER TABLE  `transactions` ADD FOREIGN KEY (  `users_id` ) REFERENCES  `expenses`.`users` (

`users_id`
) ON DELETE RESTRICT ON UPDATE RESTRICT ;

Open in new window

0
 
LVL 1

Author Comment

by:LB1234
ID: 39490604
table
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 46

Expert Comment

by:Chris Stanyon
ID: 39490767
Because you're adding a foreign key, you need to make sure that the data in the child table already exists in the parent table. In your case, every users_id that exists in the transactions table must already exist in the users table. The datatype has to match as well.
0
 
LVL 53

Assisted Solution

by:COBOLdinosaur
COBOLdinosaur earned 600 total points
ID: 39490793
Okay you have an auto increment key and it is likely that some rows do not exist for every id. In that case the query fails because there is no value for the foreign key.

There is a closed mysql bug report which appears to have the code for an error handler that may help you.
http://bugs.mysql.com/bug.php?id=68831

Cd&
0
 
LVL 1

Author Comment

by:LB1234
ID: 39490795
I have exactly one user in the users_id with a value of "64" (previous test users were erased).  In the transactions table i have several dummy transactions and have a column called "users_id" which is populated with all zeroes.  Do I change these all to "64"?  The data type is the same.  Thanks
0
 
LVL 1

Author Comment

by:LB1234
ID: 39490809
COBOL, i'm not clear on which columns in which table your first statement refers.   Can you clarify?   thanks.
0
 
LVL 46

Accepted Solution

by:
Chris Stanyon earned 1400 total points
ID: 39490830
You do need to change the values - you can't have a record in a child table that doesn't have a matching record in a parent table - that's the whole point of a FOREIGN KEY. If you have 1 parent record with a user_id of 64, then the only records that are allowed in the child table must also have a user_id of 64.
0
 
LVL 1

Author Comment

by:LB1234
ID: 39490840
Thanks Chris and Cobol!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question