Error #1452 when trying to insert foreign key

Question

I'm trying to insert a foreign key in my table A (in phpmyadmin):

ALTER TABLE A ADD FOREIGN KEY (id_B) REFERENCES B(id_B);

But I get the error:

1452 - Cannot add or update a child row: a foreign key constraint fails (`database`.<result 2 when explaining filename '#sql-edf_534aa1'>, CONSTRAINT `#sql-edf_534aa1_ibfk_1` FOREIGN KEY (`id_B`) REFERENCES `B` (`id_B`))

Table A has id_A as primary key and Table B has id_B as primary key. Both table have innoDB as storage engine and the columns id_B in table A and id_B in table B have the identical types.

Whats in wrong and how can I fix it?

Do the two tables have data in them already? If so, adding the constraint will check that it actually holds true. Check that all id_b values in table A exist in table B — Shadow Radiance

Jim Jim · Accepted Answer · 2014-09-26T14:33:28

Table A likely links to an invalid id_B:

There exists an id_B in table A that does not exist in the table B id_b column.

Error #1452 when trying to insert foreign key

1452 - Cannot add or update a child row: a foreign key constraint fails (database.<result 2 when explaining filename '#sql-edf_534aa1'>, CONSTRAINT #sql-edf_534aa1_ibfk_1 FOREIGN KEY (id_B) REFERENCES B (id_B))

1 Answers

1452 - Cannot add or update a child row: a foreign key constraint fails (`database`.<result 2 when explaining filename '#sql-edf_534aa1'>, CONSTRAINT `#sql-edf_534aa1_ibfk_1` FOREIGN KEY (`id_B`) REFERENCES `B` (`id_B`))