Getting ERROR 1452: Cannot add or update a child row: a foreign key constraint fails when using 1 our 4 insert statements

0
votes

I am trying to insert data into my DB and I keep getting the "ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails" on 1 insert command. I believe it has to do with the FKs I used when I created the table. But I am not sure how to fix it. Any suggestions would be awesome.

Original Create table commands: REATE TABLE Donut (DonutID int, DonutName VARCHAR(25) not null, Description VARCHAR(25) not null, UnitPrice decimal(4,2) not null, PRIMARY KEY (DonutID) ) ;

CREATE TABLE Customer ( CustomerID INT not null, FirstName VARCHAR(20) not null, LastName VARCHAR(20) not null, StreetAddress VARCHAR(20) not null, Apt VARCHAR(20), City VARCHAR(20) not null, State VARCHAR(2) not null, ZipCode VARCHAR(5) not null, HomePhone VARCHAR(10) not null, MobilePhone VARCHAR(10) not null, OtherPhone VARCHAR(10), PRIMARY KEY(CustomerID) ) ;

CREATE TABLE Orders ( OrderID int not null, CustomerID int, OrderDate Date not null, Notes VARCHAR(100), PRIMARY KEY (OrderID), FOREIGN KEY (CustomerID) REFERENCES Customer (CustomerID) ) ;

CREATE TABLE OrderItem ( OrderID INT, DonutID INT, Qty SMALLINT not null, PRIMARY KEY(OrderID, DonutID), FOREIGN KEY (OrderID) REFERENCES Orders (CustomerID), FOREIGN KEY (DonutID) REFERENCES Donut (DonutID) ) ;

Insert command that is failing: INSERT INTO OrderItem (OrderID,DonutID,Qty) VALUES (991,1,12), (992,2,500), (993,3,6);

2
mysql

2 Answers

0
votes

This error is probably happening because one or both of your inserted records refer to records in the Orders and/or Donut tables which do not exist.

Try running the following queries:

SELECT *
FROM Orders
WHERE CustomerID IN (991, 992);

and this one:

SELECT *
FROM Donut
WHERE DonutID IN (1, 2);

If you don't see four parent records from the two above queries, then you have the answer to your problem. The solution of course is to not refer to records which do not exist.

0
votes

I had to change The Foreign key reference for OrderID..it was referencing Orders (customerID) and it should be Reference Orders (OrderID)

CREATE TABLE OrderItem ( OrderID INT, DonutID INT, Qty SMALLINT not null, PRIMARY KEY(OrderID, DonutID), FOREIGN KEY (OrderID) REFERENCES Orders (OrderID), FOREIGN KEY (DonutID) REFERENCES Donut (DonutID) ) ;

Answered my own question!