ERROR 1452 (23000)
Met a MySQL ERROR 1452 (23000) when adding a foreign constraint on table orders:
mysql> ALTER TABLE orders ADD CONSTRAINT orders_cid_fk FOREIGN KEY (cid) REFERENCES customers (id);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`database_name`.`#sql-xxxx`, CONSTRAINT `orders_cid_fk` FOREIGN KEY (`cid`) REFERENCES `customers` (`id`))
ERROR 1452 (23000) means that some values in child column cid of the referencing table orders are not in the parent column id of the referenced table customers. It already has violated the rule of constraint before we actually create it. Therefore, MySQL was unable to create the constraint.
Solution
So now, you have to identify the rows that violate the constraint and correct them. Is it a hard work? Not really. You may use the following statement to identity the rows:
mysql> select * from orders where cid not in (select distinct id from customers);
In the above statement, we use a subquery to retrieve all the legal parent values of id from the referenced table customers as the filter, and select all the child values of cid that are not in the legal parent values from the referencing table orders. It's now your decision to delete them or update them in order to comply with the coming constraint.
After correcting all the mismatched data, you can try to add the constraint again.
mysql> ALTER TABLE orders ADD CONSTRAINT orders_cid_fk FOREIGN KEY (cid) REFERENCES customers (id);
Query OK, 249574 rows affected (0.5 sec)
Records: 249574 Duplicates: 0 Warnings: 0
It succeeded. No more ERROR 1452 (23000).
More about adding a foreign key constraint, you can refer to this post: How to Resolve MySQL ERROR 1215 (HY000): Cannot add foreign key constraint.