Ndtyjky

After some research on this problem, I found out that something is probably not right in my database structure, so I have tried the "SHOW ENGINE INNODB STATUS" command to find out what is causing the foreign key SQLException.
Here is the result on "lastest foreign key error"

If I'm right, the table that causes the problem is a temporary table, that's why I don't know what to do.

The other questions here are usually like having an unsigned int in one table, and signed int at foreign key, so that causes error, but I can't see anything like that in my script and the script itself works fine in mysql workbench, but the spring application throws exception when using this database.

The strange thing is that I don't have origin_id anywhere in my code, be it the java code or the sql.

Here is the database script:

-- MySQL Script generated by MySQL Workbench

-- Tue Oct  9 20:16:01 2018

-- Model: New Model    Version: 1.0

-- MySQL Workbench Forward Engineering



SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';



-- -----------------------------------------------------

-- DATABASE flights

-- -----------------------------------------------------

DROP DATABASE IF EXISTS `flights` ;



-- -----------------------------------------------------

-- DATABASE flights

-- -----------------------------------------------------

CREATE DATABASE `flights` ;



-- -----------------------------------------------------

-- Schema flights

-- -----------------------------------------------------

DROP SCHEMA IF EXISTS `flights` ;



-- -----------------------------------------------------

-- Schema flights

-- -----------------------------------------------------

CREATE SCHEMA IF NOT EXISTS `flights` DEFAULT CHARACTER SET utf8 ;

USE `flights` ;



-- -----------------------------------------------------

-- Table `flights`.`user`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `flights`.`user` (

  `id` REAL NOT NULL,

  `username` VARCHAR(50) NULL,

  `password` VARCHAR(50) NULL,

  `email` TEXT NULL,

  `role` VARCHAR(10) NULL DEFAULT "USER",

  `created_at` TIMESTAMP NULL,

  PRIMARY KEY (`id`))

ENGINE = InnoDB;





-- -----------------------------------------------------

-- Table `flights`.`airline`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `flights`.`airline` (

  `id` REAL NOT NULL auto_increment,

  `name` VARCHAR(100) NULL,

  `created_at` TIMESTAMP NULL,

  PRIMARY KEY (`id`))

ENGINE = InnoDB;





-- -----------------------------------------------------

-- Table `flights`.`plane`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `flights`.`plane` (

  `id` REAL NOT NULL auto_increment,

  `seats` INT NULL,

  `airline_id` REAL NULL,

  `created_at` TIMESTAMP NULL,

  PRIMARY KEY (`id`),

  CONSTRAINT `airline_id`

    FOREIGN KEY (`airline_id`)

    REFERENCES `flights`.`airline` (`id`)

    ON DELETE CASCADE

    ON UPDATE CASCADE)

ENGINE = InnoDB;



CREATE INDEX `airline_id_idx` ON `flights`.`plane` (`airline_id` ASC) VISIBLE;





-- -----------------------------------------------------

-- Table `flights`.`airport`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `flights`.`airport` (

  `id` REAL NOT NULL auto_increment,

  `name` VARCHAR(100) NULL,

  `city` VARCHAR(50) NULL,

  `country` VARCHAR(50) NULL,

  `created_at` TIMESTAMP NULL,

  PRIMARY KEY (`id`))

ENGINE = InnoDB;





-- -----------------------------------------------------

-- Table `flights`.`flight`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `flights`.`flight` (

  `id` REAL NOT NULL auto_increment,

  `departure_time` TIMESTAMP NULL,

  `arrival_time` TIMESTAMP NULL,

  `adult_prise` REAL NULL,

  `reduced_prise` REAL NULL,

  `plane_id` REAL NULL,

  `departure_airport_id` REAL NULL,

  `arrival_airport_id` REAL NULL,

  `created_at` TIMESTAMP NULL,

  `airline_id` REAL NOT NULL,

  PRIMARY KEY (`id`),

  CONSTRAINT `airline_id_2`

    FOREIGN KEY (`airline_id`)

    REFERENCES `flights`.`airline` (`id`)

    ON DELETE CASCADE

    ON UPDATE CASCADE,

  CONSTRAINT `plane_id`

    FOREIGN KEY (`plane_id`)

    REFERENCES `flights`.`plane` (`id`)

    ON DELETE CASCADE

    ON UPDATE CASCADE,

  CONSTRAINT `departure_airport_id`

    FOREIGN KEY (`departure_airport_id`)

    REFERENCES `flights`.`airport` (`id`)

    ON DELETE CASCADE

    ON UPDATE CASCADE,

  CONSTRAINT `arrival_airport_id`

    FOREIGN KEY (`arrival_airport_id`)

    REFERENCES `flights`.`airport` (`id`)

    ON DELETE CASCADE

    ON UPDATE CASCADE)

ENGINE = InnoDB;



CREATE INDEX `plane_id_idx` ON `flights`.`flight` (`plane_id` ASC) VISIBLE;



CREATE INDEX `departure_airport_id_idx` ON `flights`.`flight` (`departure_airport_id` ASC) VISIBLE;



CREATE INDEX `arrival_airport_id_idx` ON `flights`.`flight` (`arrival_airport_id` ASC) VISIBLE;





-- -----------------------------------------------------

-- Table `flights`.`reservation`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `flights`.`reservation` (

  `id` REAL NOT NULL,

  `reservation_time` TIMESTAMP NULL,

  `adult_ticket` SMALLINT NULL,

  `reduced_ticket` SMALLINT NULL,

  `user_id` REAL NOT NULL,

  `flight_id` REAL NOT NULL,

  `created_at` TIMESTAMP NULL,

  PRIMARY KEY (`id`),

  CONSTRAINT `user_id`

    FOREIGN KEY (`user_id`)

    REFERENCES `flights`.`user` (`id`)

    ON DELETE CASCADE

    ON UPDATE CASCADE,

  CONSTRAINT `flight_id`

    FOREIGN KEY (`flight_id`)

    REFERENCES `flights`.`flight` (`id`)

    ON DELETE CASCADE

    ON UPDATE CASCADE)

ENGINE = InnoDB;



CREATE INDEX `user_id_idx` ON `flights`.`reservation` (`user_id` ASC) VISIBLE;



CREATE INDEX `flight_id_idx` ON `flights`.`reservation` (`flight_id` ASC) VISIBLE;



USE `flights` ;



-- -----------------------------------------------------

-- Placeholder table for view `flights`.`v_airline`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `flights`.`v_airline` (`id` REAL, `name` VARCHAR(30), `created_at` TIMESTAMP);



-- -----------------------------------------------------

-- Placeholder table for view `flights`.`v_airport`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `flights`.`v_airport` (`id` REAL, `name` VARCHAR(100), `city` VARCHAR(50), `country` VARCHAR(50), `created_at` TIMESTAMP);



-- -----------------------------------------------------

-- Placeholder table for view `flights`.`v_flight`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `flights`.`v_flight` (`id` REAL, `departure_time` TIMESTAMP, `arrival_time` TIMESTAMP, `adult_prise` REAL, `reduced_prise` REAL, `plane_id` REAL, `departure_airport_id` REAL, `arrival_airport_id` REAL, `created_at` TIMESTAMP);



-- -----------------------------------------------------

-- Placeholder table for view `flights`.`v_plane`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `flights`.`v_plane` (`id` REAL, `seats` INT, `airline_id` REAL, `created_at` TIMESTAMP);



-- -----------------------------------------------------

-- Placeholder table for view `flights`.`v_reservation`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `flights`.`v_reservation` (`id` REAL, `reservation_time` TIMESTAMP, `adult_ticket` SMALLINT, `reduced_ticket` SMALLINT, `user_id` REAL, `flight_id` REAL, `created_at` TIMESTAMP);



-- -----------------------------------------------------

-- Placeholder table for view `flights`.`v_user`

-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `flights`.`v_user` (`id` REAL, `username` VARCHAR(50), `password` VARCHAR(50), `email` TEXT, `role` VARCHAR(50), `created_at` TIMESTAMP);



-- -----------------------------------------------------

-- View `flights`.`v_airline`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `flights`.`v_airline`;

USE `flights`;

CREATE  OR REPLACE VIEW `v_airline` AS select * from airline;



-- -----------------------------------------------------

-- View `flights`.`v_airport`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `flights`.`v_airport`;

USE `flights`;

CREATE  OR REPLACE VIEW `v_airport` AS select * from airport;



-- -----------------------------------------------------

-- View `flights`.`v_flight`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `flights`.`v_flight`;

USE `flights`;

CREATE  OR REPLACE VIEW `v_flight` AS select * from flight;



-- -----------------------------------------------------

-- View `flights`.`v_plane`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `flights`.`v_plane`;

USE `flights`;

CREATE  OR REPLACE VIEW `v_plane` AS select * from plane;



-- -----------------------------------------------------

-- View `flights`.`v_reservation`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `flights`.`v_reservation`;

USE `flights`;

CREATE  OR REPLACE VIEW `v_reservation` AS select * from reservation;



-- -----------------------------------------------------

-- View `flights`.`v_user`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `flights`.`v_user`;

USE `flights`;

CREATE  OR REPLACE VIEW `v_user` AS select * from user;



SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;