0

I have a parent orders table like this:

+---------------+---------------+------+-----+-----------+----------------+
| Field         | Type          | Null | Key | Default   | Extra          |
+---------------+---------------+------+-----+-----------+----------------+
| id            | int(11)       | NO   | PRI | NULL      | auto_increment |
| pnref         | varchar(50)   | YES  | UNI | NULL      |                |
| customerid    | int(11)       | YES  |     | NULL      |                |
+-------------------------------------------------------------------------+

I have a child orders_content table like this:

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int(6)       | NO   | PRI | NULL    | auto_increment |
| orderid          | int(11)      | NO   |     | NULL    |                |
| status           | varchar(50)  | YES  |     | pending |                |
+------------------+--------------+------+-----+---------+----------------+

I am attempting to add aFOREIGN KEY like this:

ALTER TABLE orders_content 
ADD CONSTRAINT fk_orders_content 
FOREIGN KEY(orderid) REFERENCES orders(id)
ON DELETE CASCADE ON UPDATE CASCADE;

I cannot resolve why this fails:

1452 - Cannot add or update a child row: a foreign key constraint fails (gls.#sql-1744_4d58, CONSTRAINTfk_orders_contentFOREIGN KEY (orderid) REFERENCESorders(id) ON DELETE CASCADE ON UPDATE CASCADE)

If anyone can see my error or errors I would appreciate it.

JimB814
  • 510
  • 8
  • 24
  • 1
    You most likely have some value in `orderid` column which does not exist in the `id` column of the `orders` table. – Madhur Bhaiya Oct 30 '18 at 14:50
  • The definition of duplicate is subjective. On many occasions I have found solutions in questions adjudicated duplicate.While there is value in encouraging due diligence before asking, questions that appear similar should not be narrowly interpreted as conclusive evidence that due diligence was neglected. Each of us reaches enlightenment uniquely. In my case, I studied several posted solutions, but not until reading Madhur Bhaiya's comment on my question did I understand that data in `orders`.`id` must match data in `orders_content`.`orderid`. Then, re-reading the cited post, it made sense. – JimB814 Oct 31 '18 at 12:14
  • You can use the following query to find the entries causing the FK constraint failure: `SELECT orders_content.* FROM orders_content LEFT JOIN orders ON orders.id = orders_content.orderid WHERE orders.id IS NULL;` – Madhur Bhaiya Oct 31 '18 at 12:23
  • 1
    Thanks Madhur! You read my mind. That's a fabulous query that I did not know was possible, and precisely retrieves the data I was wondering how to see. Fixing the non-matches is the necessary step to allow me to add the `FOREIGN KEY` that is needed. Thanks for taking the time to send this. – JimB814 Oct 31 '18 at 14:28

0 Answers0