I have two tables as below
1 : Table Name : parent Columns : parent_id(Primary Key) , title
2 : Table Name : child Columns : child_id(Primary Key) , title , fk_pid(Indexed)
I want to make reference between prent.parent_id and child.fk_pid . I have set type of both table to InnoDB. I have opened 'relation view' in the structure of child table. Then I have selected foreign key constraint for child.fk_pid and set to parent.parent_id and set options as below
constraint name : myFK on delete : restrict on update : restrict
but when I am clicking on save button, PHPMyAdmin is throwing following error.
#1452 - Cannot add or update a child row: a foreign key constraint fails (`fk`.`#sql-168_5f`, CONSTRAINT `myFk` FOREIGN KEY (`fk_pid`) REFERENCES `parent` (`parent_id`))
please help me
MySQL won't create the foreign key if you have fk_pid in the child table that don't exist as id in the parent table. Check the child table for any references to a parent id that do not exist in the parent table.
select fk_pid from child left join parent on child.fk_pid = parent.id where parent.id is null;