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

[nonshatter]

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

[nonshatter]

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]

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
[e-mail]

[smantscheff]

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.