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` (
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`))
c_id smallint(5) unsigned NO PRI NULL auto_increment
c_category varchar(50) YES NULL
c_parentid smallint(5) unsigned YES NULL
p_id smallint(5) UNSIGNED No auto_increment
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:
I hope that helps if anyone else has a similar problem.
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
Saif Ad'den Dwekat
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.