0

Here is the statement I am executing and related error, any hints what is wrong and how to debug further is appreciated. Using MySQL Workbench/MySQL.

Especially confused what means child row here? How foreign key related to child row? And what is the child row here?

ALTER TABLE Orders
ADD CONSTRAINT fk_Customer FOREIGN KEY (CustomerID) 
REFERENCES Customers(CustomerID)

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (test.#sql-ff_2, CONSTRAINT fk_Customer FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID))

Mureinik
  • 297,002
  • 52
  • 306
  • 350
Lin Ma
  • 9,739
  • 32
  • 105
  • 175
  • 1
    might [this](http://stackoverflow.com/questions/22210461/mysql-error-code-1452-foreign-key-constraint) or [this](http://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa) help? – SomeJavaGuy Aug 26 '15 at 07:16
  • @KevinEsche, my issue happens when define foreign key constraint, and the issue you referred to is dealing with errors during insert? Please feel free to correct me. – Lin Ma Aug 26 '15 at 07:19
  • 1
    check out the second link, be sure that all ids you are trying to refer to are existing in the refering table aswell. – SomeJavaGuy Aug 26 '15 at 07:22

1 Answers1

1

This error means that your tables contain data that should not be allowed by the foreign key you're trying to create. You could use a query to find them:

SELECT *
FROM   orders
WHERE  customerid NOT IN (SELECT customerid FROM customers)

If you're sure these rows are indeed faulty, you could use a similar delete statement to remove them:

DELETE FROM orders
WHERE  customerid NOT IN (SELECT customerid FROM customers)
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • thanks and met with error, Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE customerID NOT IN (SELECT customerid FROM Orders)' at line 2 – Lin Ma Aug 26 '15 at 07:47
  • 1
    @LinMa on which statement? the `select` or the `delete`? – Mureinik Aug 26 '15 at 07:48
  • Hi Mureinik, for delete statement. – Lin Ma Aug 26 '15 at 07:49
  • 1
    @LinMa for some reason I missed the `from` keyword. Fixed - please see my updated answer. – Mureinik Aug 26 '15 at 07:50