MySQL Error 1452 (23000): Cannot add or update a child row

Question

I need a help with this problem. I don't understand how to resolve this problem. I will be happy if you find out why this problem happens. Here is my SQL:

Create database Order_Purchase;
Use Order_Purchase;

Create table Customer (
customerID      int(10),
cname           varchar(30),
city            varchar(30),
Constraint customer_PK Primary Key (customerID))
Engine = innodb;

Create table Item (
itemID          int(10),
description     varchar(30),    
price           double,
Constraint item_PK Primary Key (itemID))
Engine = innodb;

Create table Order_ (
orderID         int(10),
customerID      int(10),
orderDate       date,
Constraint order_PK Primary Key (orderID),
Constraint order_FK1 Foreign Key (customerID) references Customer(customerID))
Engine = innodb;

Create table Order_Item (
orderID         int(10) null default null,
itemID          int(10) null default null,
quantity        int,
Constraint order_item_PK Primary Key (orderID, itemID),
Constraint order_item_FK1 Foreign Key (orderID) references Order_(orderID),
Constraint order_item_FK2 Foreign Key (itemID) references Item(itemID))
Engine = innodb;

Create table Warehouse (
warehouseID     int(10),
city            varchar(30),
Constraint warehouse_PK Primary Key (warehouseID))
Engine = innodb;

Create table Shipment (
orderID         int(10), 
warehouseID     int(10),
shipDate        date,
Constraint shipment_PK Primary Key (orderID, warehouseID),
Constraint shipment_FK1 Foreign Key (orderID) references Order_(orderID),
Constraint shipment_FK2 Foreign Key (warehouseID) references Warehouse(warehouseID))
ENGINE = innodb;

My code for inserting:

Insert into Order_Item (orderID, itemID, quantity) values (10000, 105472, 1);

Insert into Order_Item (orderID, itemID, quantity) values (10001, 105472, 4);

Insert into Order_Item (orderID, itemID, quantity) values (10002, 104375, 1);

Insert into Order_Item (orderID, itemID, quantity) values (10003, 100870, 2);

Insert into Order_Item (orderID, itemID, quantity) values (10003, 103798, 1);

Insert into Order_Item (orderID, itemID, quantity) values (10004, 103798, 1);

Insert into Order_Item (orderID, itemID, quantity) values (10004, 105472, 2);

Insert into Order_Item (orderID, itemID, quantity) values (10005, 100475, 1);

Insert into Order_Item (orderID, itemID, quantity) values (10006, 100870, 1);

Insert into Order_Item (orderID, itemID, quantity) values (10006, 105472, 1);

Insert into Order_Item (orderID, itemID, quantity) values (10006, 103798, 1);

The result:

mysql> Insert into Order_Item (orderID, itemID, quantity) values (10000, 105472, 1);
Query OK, 1 row affected (0.05 sec)

mysql> Insert into Order_Item (orderID, itemID, quantity) values (10001, 105472, 4);
Query OK, 1 row affected (0.01 sec)

mysql> Insert into Order_Item (orderID, itemID, quantity) values (10002, 104375, 1);
Query OK, 1 row affected (0.01 sec)

mysql> Insert into Order_Item (orderID, itemID, quantity) values (10003, 100870, 2);
Query OK, 1 row affected (0.00 sec)

mysql> Insert into Order_Item (orderID, itemID, quantity) values (10003, 103798, 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`order_purchase`.`order_item`, CONSTRAINT `o
rder_item_FK2` FOREIGN KEY (`itemID`) REFERENCES `item` (`itemID`))
mysql> Insert into Order_Item (orderID, itemID, quantity) values (10004, 103798, 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`order_purchase`.`order_item`, CONSTRAINT `o
rder_item_FK2` FOREIGN KEY (`itemID`) REFERENCES `item` (`itemID`))
mysql> Insert into Order_Item (orderID, itemID, quantity) values (10004, 105472, 2);
Query OK, 1 row affected (0.01 sec)

score 3 · Accepted Answer · answered Nov 16 '13 at 22:53

3

As the error tells you, you need to first insert values in the item table. Run the following insert statement before the failing one:

INSERT INTO Item(itemID, description) values(103798, 'This was the missing item');

Now, you should be able to insert the intended row into the Order_Item table.

answered Nov 16 '13 at 22:53

Chandranshu

3,669
3
20
37

Thank you so much! I finally found a problem. =) – deafprogrammer24 Nov 16 '13 at 23:58

MySQL Error 1452 (23000): Cannot add or update a child row

1 Answers1

Linked