CODEWITHSUNDEEP
mysqlsqldatabase
Migro96
Migro96

Reputation: 59

I get this error: Cannot add or update a child row: a foreign key constraint fails

I am trying to do inserts into my database to "populate" it, and all inserts work fine, except for the "reservation" insert...
When I try to insert my reservation table:

Insert Into Reservation
(ReservNum, ReserveDate, NumOfPassengers, sheduledTime, ActualPickupTime, ActualTime, PricePaid, HourlyRate, SalaryEarned)
VALUES
('24333', '2015-10-15', '6', '20', '7:04', '22', '$15', '34', '$12.47');

I get this error:

Cannot add or update a child row: a foreign key constraint fails (`oma`.`Reservation`, CONSTRAINT `Reservation_ibfk_1` FOREIGN KEY (`Customer_CustomerID`) REFERENCES `Customer` (`CustomerID`))

Here are my create tables:

CREATE TABLE Customer (
CustomerID INT AUTO_INCREMENT,
Name VARCHAR(90) NOT NULL,
Phone VARCHAR(45) NULL,
PRIMARY KEY (CustomerID));

CREATE TABLE Location (
Address VARCHAR(100) NOT NULL,
Latitude VARCHAR(45) NOT NULL DEFAULT ' ',
Longitude VARCHAR(45) NOT NULL,
PRIMARY KEY (Address));


CREATE TABLE Employee (
EmployeeID INT AUTO_INCREMENT,
Name VARCHAR(90) NOT NULL,
PRIMARY KEY (EmployeeID));


CREATE TABLE Truck (
LicensePlate CHAR(20) NOT NULL,
color VARCHAR(45) NULL,
capacity VARCHAR(45) NULL,
PRIMARY KEY (LicensePlate));

CREATE TABLE Shifts (
ShiftTime DATETIME NOT NULL,
PRIMARY KEY (ShiftTime));

CREATE TABLE EmployeeShifts (
DesiredShift DATETIME NOT NULL,
EmployeeWorking INT NULL,
DateOfShift DATE,
PRIMARY KEY(DesiredShift, EmployeeWorking),
FOREIGN KEY (EmployeeWorking) REFERENCES Employee(EmployeeID),
FOREIGN KEY (DesiredShift) REFERENCES Shifts(ShiftTime));

CREATE TABLE Reservation (
ReservNum INT NOT NULL,
ReserveDate DATE NULL,
PickupTime VARCHAR(45) NOT NULL,
NumOfPassengers INT NULL,
sheduledTime VARCHAR(45) NULL,
ActualPickupTime VARCHAR(45),
ActualTime VARCHAR(45),
SalaryEarned VARCHAR(10),
PricePaid VARCHAR(45),
HourlyRate DECIMAL(7,2) NOT NULL,
Customer_CustomerID INT AUTO_INCREMENT,
Truck_LicensePlate char(20) NOT NULL,
Employee_EmployeeID_Driver INT,
Location_Address_Pickup VARCHAR(100),
Employee_EmployeeID_Passenger INT,
Location_Address_Drop VARCHAR(100),
PRIMARY KEY (ReservNum),
FOREIGN KEY (Customer_CustomerID) REFERENCES Customer (CustomerID),
FOREIGN KEY (Truck_LicensePlate) REFERENCES Truck (LicensePlate),
FOREIGN KEY (Employee_EmployeeID_Driver) REFERENCES Employee (EmployeeID),
FOREIGN KEY (Location_Address_Pickup) REFERENCES Location (Address),
FOREIGN KEY (Employee_EmployeeID_Passenger) REFERENCES Employee (EmployeeID),
FOREIGN KEY (Location_Address_Drop) REFERENCES Location (Address));

Upvotes: 0

Views: 474

Answers (1)

Kenney
Kenney

Reputation: 9093

The Customer_Customer_ID column in Reservation is an AUTO INCREMENT: 

Customer_CustomerID INT AUTO_INCREMENT,

meaning that if you don't specify the value when INSERT-ing, one will be automatically assigned to it. You didn't specify it in the INSERT and so the value automatically assigned to it didn't exist in the Customers table, which violated the FOREIGN KEY constraint:

FOREIGN KEY (Customer_CustomerID) REFERENCES Customer (CustomerID),

To solve this,

  • Remove the AUTO_INCREMENT from the Customer_CustomerID column. You should be able to insert now, since the column can be NULL, in which case the FOREIGN KEY is not an issue.

  • Or, if you want to assign a Customer to the Reservation, make sure a row exists in the Customer table, and pass the Customer_Id to the INSERT INTO Reservation. For instance, like this:

    INSERT INTO Customers( Name ) VALUES ('Test');

INSERT INTO Reservation (
    ReservNum, Customer_Customer_ID,
    ReserveDate, NumOfPassengers, 
    sheduledTime, ActualPickupTime, ActualTime,
    PricePaid, HourlyRate, SalaryEarned
)
VALUES (
    '24333', LAST_INSERT_ID(),
    '2015-10-15', '6',
    '20', '7:04', '22',
    '$15', '34', '$12.47'
);

The LAST_INSERT_ID() gets the value from the AUTO_INCREMENT column in the last INSERT.

Upvotes: 1

Related Questions