mysql - MySQL无法添加外键？

Question

这是我正在执行的语句和相关错误，任何提示错误以及如何进一步调试的提示都值得赞赏。使用 MySQL Workbench/MySQL。

特别困惑这里的子行是什么意思？外键如何与子行关联？这里的子行是什么？

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

Answer 1

此错误意味着您的表包含您尝试创建的外键不允许的数据。您可以使用查询来查找它们:

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

如果您确定这些行确实有错误，您可以使用类似的 delete 语句来删除它们:

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