mysql - 我收到此错误 : Cannot add or update a child row: a foreign key constraint fails

Question

我正在尝试插入到我的数据库中以“填充”它，并且所有插入都工作正常，除了“保留”插入...
当我尝试插入预订表时:

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

我收到此错误:

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

这是我创建的表:

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

Answer 1

Reservation 中的 Customer_Customer_ID 列是一个 AUTO INCREMENT:

Customer_CustomerID INT AUTO_INCREMENT,

这意味着如果您在 INSERT 时未指定值，则会自动为其分配一个值。您没有在 INSERT 中指定它，因此自动分配给它的值在 Customers 表中不存在，这违反了 FOREIGN KEY约束:

FOREIGN KEY (Customer_CustomerID) REFERENCES Customer (CustomerID),

为了解决这个问题，

• 从 Customer_CustomerID 列中删除 AUTO_INCRMENT。您现在应该可以插入，因为该列可以为 NULL，在这种情况下，FOREIGN KEY 不是问题。

• 或者，如果您想将客户分配给预订，请确保 Customer 表中存在一行，并将 Customer_Id 传递给 插入预订。例如，像这样:

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

LAST_INSERT_ID()从最后一个 INSERT 中的 AUTO_INCRMENT 列获取值。