Error: 1822 Failed to add the foreign key constraint

SQL
1

I’m making my database for the project flight, but I keep getting this one error:

ERROR: Error 1822: Failed to add the foreign key constraint. Missing index for constraint ‘fk_tickets_flight’ in the referenced table ‘flight’.

Anyone else getting this? I have ‘fk_tickets_flight’ in the Tickets table, but for some reason MySQL won’t send it to the Flights table.

Here’s my code:

SQL Code:
– -----------------------------------------------------
– Table project_flight.tickets
– -----------------------------------------------------

   CREATE TABLE IF NOT EXISTS `project_flight`.`tickets` (
      `flight_name` VARCHAR(50) NOT NULL,
      `first_name` VARCHAR(50) NOT NULL,
      `flight_number` VARCHAR(50) NOT NULL,
      `ticket_number` VARCHAR(50) NOT NULL,
      `price` DECIMAL(5,2) NOT NULL,
      `confirmation_number` VARCHAR(250) NOT NULL,
      INDEX `fk_tickets_flight_classes_idx` (`flight_name` ASC) VISIBLE,
      INDEX `fk_tickets_passenger1_idx` (`first_name` ASC) VISIBLE,
      PRIMARY KEY (`flight_number`, `first_name`, `flight_name`),
      INDEX `fk_tickets_flight1_idx` (`flight_number` ASC) VISIBLE,
      CONSTRAINT `fk_tickets_flight_classes`
        FOREIGN KEY (`flight_name`)
        REFERENCES `project_flight`.`flight_classes` (`name`)
        ON DELETE NO ACTION
        ON UPDATE CASCADE,
      CONSTRAINT `fk_tickets_passenger`
        FOREIGN KEY (`first_name`)
        REFERENCES `project_flight`.`passenger` (`first_name`)
        ON DELETE NO ACTION
        ON UPDATE CASCADE,
      CONSTRAINT `fk_tickets_flight`
        FOREIGN KEY (`flight_number`)
        REFERENCES `project_flight`.`flight` (`number`)
        ON DELETE NO ACTION
        ON UPDATE CASCADE)
    ENGINE = InnoDB

SQL script execution finished: statements: 9 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

2

The message says that you don’t have an index on “number” in your “flights” table.

And BTW: Are you sure that you can uniquely identify a passenger by it’s first name?

3

No I can’t, it’s best I use a customer id instead, I should’ve thought of that earlier.

Though after inserting customer_id I have new problem. It says I have "Error 3734: Failed to add the foreign key constraint. Missing column ‘customer_id’ for constraint ‘fk_tickets_passenger’ in the referenced table ‘passenger’ ". And it doesn’t make any sense because if you look at my screen shot, I do have customer_id as a column for the constraint.

fk_tickets
fk_tickets1119×294 12.4 KB

So how am I getting this error?

4

Check if you really have a customer_id column in your passenger table.

5

Right here.

customer_id
customer_id943×397 15.1 KB

6

No idea. Can’t you provide the sql scripts for both tables instead of screenshot snippets?

7

Executing SQL script in server
ERROR: Error 3734: Failed to add the foreign key constraint. Missing column ‘customer_id’ for constraint ‘fk_tickets_passenger’ in the referenced table ‘passenger’
SQL Code:
– -----------------------------------------------------
– Table project_flight.tickets
– -----------------------------------------------------
CREATE TABLE IF NOT EXISTS project_flight.tickets (
flight_name VARCHAR(50) NOT NULL,
ticketscol VARCHAR(45) NOT NULL,
customer_id VARCHAR(50) NOT NULL,
ticket_number VARCHAR(50) NOT NULL,
price DECIMAL(5,2) NOT NULL,
confirmation_number VARCHAR(250) NOT NULL,
flight_air_line VARCHAR(50) NOT NULL,
INDEX fk_tickets_flight_classes_idx (flight_name ASC) VISIBLE,
PRIMARY KEY (flight_name, ticketscol, flight_air_line),
INDEX fk_tickets_passenger_idx (customer_id ASC) VISIBLE,
INDEX fk_tickets_flight_idx (flight_air_line ASC) VISIBLE,
CONSTRAINT fk_tickets_flight_classes
FOREIGN KEY (flight_name)
REFERENCES project_flight.flight_classes (name)
ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT fk_tickets_passenger
FOREIGN KEY (customer_id)
REFERENCES project_flight.passenger (customer_id)
ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT fk_tickets_flight1
FOREIGN KEY (flight_air_line)
REFERENCES project_flight.flight (air_line)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB

SQL script execution finished: statements: 9 succeeded, 1 failed