1,105,169 Community Members

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

Member Avatar
nonshatter
Posting Whiz
377 posts since Nov 2009
Reputation Points: 26 [?]
Q&As Helped to Solve: 60 [?]
Skill Endorsements: 0 [?]
 
0
 

Hey,

I am having a really annoying problem when trying to set up a foreign key in my database... I have two tables - Sell and Category

I want the 'c_id' from the Category table to be the foreign key for 'p_id' in the Sell table. Both tables are InnoDB and both fields are the same datatype. Here is the query and the error:

ALTER TABLE `sell` ADD FOREIGN KEY ( `p_id` ) REFERENCES `ece60363`.`category` (
`c_id`
);

MySQL said: Documentation
#1452 - Cannot add or update a child row: a foreign key constraint fails (`ece60363/#sql-1f3b_937c`, CONSTRAINT `#sql-1f3b_937c_ibfk_1` FOREIGN KEY (`p_id`) REFERENCES `category` (`c_id`))

Category table:
c_id smallint(5) unsigned NO PRI NULL auto_increment
c_category varchar(50) YES NULL
c_parentid smallint(5) unsigned YES NULL

Sell table:
p_id smallint(5) UNSIGNED No auto_increment
//other fields

Member Avatar
nonshatter
Posting Whiz
377 posts since Nov 2009
Reputation Points: 26 [?]
Q&As Helped to Solve: 60 [?]
Skill Endorsements: 0 [?]
 
0
 

Sorry, I've just managed to solve it, even though i'm not sure what the problem actually was! Typical.

I re-made the Sell table. I also changed the ID data types to int(11) and removed the 'unsigned' attribute from both. I then followed this tutorial:

http://www.learnphp.org/tutorials/Setting-Up-Foreign-Keys-Using-PhpMyAdmin-63466.html

I hope that helps if anyone else has a similar problem.

Xaif
Newbie Poster
1 post since Jun 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Unverified Member
 
0
 

hello people,

I think the problem that is there a mismatch in the data in both table so you should empty both tables or look for the mismatch and fix it

Your sincerely
Saif Ad'den Dwekat
snm.nabil@gmail.com

Member Avatar
smantscheff
Nearly a Posting Virtuoso
1,289 posts since Oct 2010
Reputation Points: 265 [?]
Q&As Helped to Solve: 271 [?]
Skill Endorsements: 8 [?]
 
0
 

The problem probably was not a data mismatch but a data type mismatch. InnoDB requires exactly the same data type in relations, so you cannot link an integer(11) to an integer(5) field.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article