Mysql Error 1452: Can not add or update a child row: a foreign key

Question

---

 I have THE FOLLOWING 

 

[U] [B] TABLE1 [/ B] [/ U] Indices: code, provider code: varchar (15) supplier: int (4) 

 

[U] [B] TABLE 2 [/ B] [/ U] Indices: code, provider code: varchar (15) supplier: int (4) 

 

And try to put a foreign key with this command 

 

ALTER TABLE table1 ADD CONSTRAINT FOREIGN KEY `` `FK_tabla1 FK_tabla1` (`code`, `supplier`) REFERENCES `table2` (`code`, `supplier`) ON DELETE NO ACTION ON UPDATE NO ACTION; 

And all the while I get the error 

 

Can not add or update a child row: a foreign key constraint fails (`XXXXXXXXX / # sql-810_70491`, CONSTRAINT `fk_tabla1` FOREIGN KEY (`code`, `supplier`) REFERENCES `table2` (`code`, `supplier` ) ON DELETE NO ACTION ON UPDATE 

Not to do ... 

 

Thanks 

---

Answer 1

---

You are trying to add a foreign key when no referential integrity. In table1 have code-provider couples who are not in table2 ... like the relationship is reversed? 

 

Anyway note that when defining a multi reference key field references must be given together within the same row, so if you table2 (1, 1), (1, 2), (2, 3 ) and table 1 (1, 1), (1, 2), (1, 3) the tuple (1, 3) is not present in table2 and will not leave. 

 

To see how many rows do not meet the integrity, you can launch a 

 

SELECT COUNT (*) AS count FROM table1 t1 LEFT JOIN table2 t2 ON t1.codigo = t2.codigo t1.proveedor = t2.proveedor AND WHERE IS NULL t2.codigo 

If not many can remove the COUNT and do you return the t1. * And so see what they are. 

 

Greetings.

---