Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2013-09-13
9
Medium Priority
?
23,880 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 45

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 45

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

564 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