CODEWITHSUNDEEP
mysqlsqlforeign-keysddl
Lin Ma
Lin Ma

Reputation: 10159

Cannot add foreign key in MySQL?

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))

Upvotes: 0

Views: 73

Answers (1)

Mureinik
Mureinik

Reputation: 312136

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)

Upvotes: 1

Related Questions