MySql - Cannot add or update a child foreign key constraint fails

Question

I have two tables, 'booking' and 'isBooked'. I want to add values to the isBooked table. But i keep getting an error "Cannot add or update a child foreign key constraint fails".

CREATE TABLE booking (
    bookingID INT AUTO_INCREMENT,
    customerID INT,
    startDate DATE,
    endDate DATE,
    dateBookedOn  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    employeeID int,
    PRIMARY KEY (bookingID),
    INDEX idx_start (startDate),
    INDEX idx_end (endDate),
    FOREIGN KEY (customerID) REFERENCES customer(CustomerID)
);

CREATE TABLE isBooked(
    BookingID int,
    DogID int,
    RunID int,
    foreign key (RunID) references Run(RunID),
    foreign key (DogID) references Dog(dogID),
    foreign key (BookingID) references Booking(BookingID)
);


insert into isbooked values(1, 1, 1);

can anyone tell me why this error appears when trying to insert values to 'isBooked'.

You are trying to insert values in isBooked that does not exist in the referred table booking. — juanmajmjr, Jul 19 '15 at 19:09
the insert attempt violates Run, dog, or booking constraints (at least 1) — Drew, Jul 19 '15 at 19:12
@DrewPierce, Yes it violates one of them but the user only showed 2 tables! — juanmajmjr, Jul 19 '15 at 19:15
even if he showed us 3 parent tables, we can't see the missing data in the 3 — Drew, Jul 19 '15 at 19:16

score 2 · Answer 1 · answered Jul 19 '15 at 19:16

Your isbooked table has foreign key constraints on references to three other tables:

foreign key (RunID) references Run(RunID),
foreign key (DogID) references Dog(dogID),
foreign key (BookingID) references Booking(BookingID)

This constraint makes sure that whatever value you enter, it needs to match one of the rows in referenced tables.

Therefore, the reason you're getting this error is because there is no matching records in one (or maybe all) referenced tables. First you need to add Run, Dog and Booking with IDs you're using in the insert and only then you can add a record to isbooked.

Good, clear, complete and fashionable! – juanmajmjr Jul 19 '15 at 19:24 — juanmajmjr, Jul 19 '15 at 19:24

MySql - Cannot add or update a child foreign key constraint fails

1 Answers1