I have a project to do for my school on databases. Firstly, I've created the Logical and Relational databases with Oracle(data modeler) and afterwards I generated the code. That code was converted into mySQL via SQLines converter. When I open Xampp and phpmyamdin to run the database an error seems to be occurred.
Code:
CREATE TABLE address (
country CHAR(40) NOT NULL,
street CHAR(40) NOT NULL,
city CHAR(40) NOT NULL,
region CHAR(40) NOT NULL,
postal_code INTEGER NOT NULL,
customer_customer_id INTEGER NOT NULL,
country_fax_code VARCHAR(40) NOT NULL
);
CREATE UNIQUE INDEX address__idx ON
address (
customer_customer_id
ASC );
ALTER TABLE address ADD CONSTRAINT address_pk PRIMARY KEY ( country );
CREATE TABLE customer (
customer_id INTEGER NOT NULL,
full_name CHAR(50) NOT NULL,
email VARCHAR(40),
address_country CHAR(40) NOT NULL
);
CREATE UNIQUE INDEX customer__idx ON
customer (
address_country
ASC );
ALTER TABLE customer ADD CONSTRAINT customer_pk PRIMARY KEY ( customer_id );
CREATE TABLE customer_reservation (
customer_customer_id INTEGER NOT NULL,
country_fax_code VARCHAR(40) NOT NULL,
reservations_reservation_id INTEGER NOT NULL,
reservations_flight_id1 INTEGER NOT NULL,
reservations_reservation_id1 INTEGER NOT NULL
);
CREATE TABLE fax (
country_fax_code VARCHAR(10) NOT NULL,
region_fax_code INTEGER NOT NULL,
local_number INTEGER NOT NULL,
customer_customer_id INTEGER,
country_fax_code1 VARCHAR(10) NOT NULL
);
ALTER TABLE fax ADD CONSTRAINT fax_pk PRIMARY KEY ( country_fax_code );
CREATE TABLE flight (
flight_id VARCHAR(40) NOT NULL,
airline_id VARCHAR(40) NOT NULL,
bussinessclass BOOLEAN NOT NULL,
smokerseats BOOLEAN NOT NULL,
flight_status_id1 BOOLEAN NOT NULL
);
CREATE UNIQUE INDEX flight__idx ON
flight (
flight_status_id1
ASC );
ALTER TABLE flight ADD CONSTRAINT flight_pk PRIMARY KEY ( flight_id );
CREATE TABLE flight_status (
flight_id INTEGER NOT NULL,
date_departure DATETIME NOT NULL,
time_departure DATETIME NOT NULL,
flight_time INTEGER NOT NULL,
total_bussinessclass INTEGER NOT NULL,
reserved_bussiness INTEGER NOT NULL,
total_economy INTEGER NOT NULL,
reserved_economy INTEGER NOT NULL,
flight_flight_id INTEGER NOT NULL,
reservation_id INTEGER NOT NULL,
flight_status_id DOUBLE NOT NULL
);
CREATE UNIQUE INDEX flight_status__idx ON
flight_status (
flight_flight_id
ASC );
ALTER TABLE flight_status ADD CONSTRAINT flight_status_pk PRIMARY KEY (
flight_status_id );
CREATE TABLE flight_taxes (
airport CHAR(40) NOT NULL,
local_currency CHAR(40) NOT NULL,
exchange_rate DOUBLE NOT NULL,
total_taxes DOUBLE NOT NULL,
reservations_reservation_id INTEGER NOT NULL,
reservations_flight_id1 INTEGER NOT NULL,
reservations_reservation_id1 INTEGER NOT NULL
);
CREATE UNIQUE INDEX flight_taxes__idx ON
flight_taxes (
reservations_reservation_id
ASC,
reservations_flight_id1
ASC,
reservations_reservation_id1
ASC );
CREATE TABLE phone (
country_phone_code VARCHAR(10) NOT NULL,
region_phone_code INTEGER NOT NULL,
local_number INTEGER NOT NULL,
customer_customer_id INTEGER NOT NULL,
country_fax_code VARCHAR(10) NOT NULL
);
ALTER TABLE phone ADD CONSTRAINT phone_pk PRIMARY KEY ( country_phone_code );
CREATE TABLE reservations (
reservation_id INTEGER NOT NULL,
date_of_reservation DATETIME NOT NULL,
flight_id INTEGER NOT NULL,
datetime_departure DATETIME(6) NOT NULL,
datetime_arrival DATETIME(6) NOT NULL,
seat_type CHAR(40) NOT NULL,
total_price DOUBLE NOT NULL,
flight_cost DOUBLE NOT NULL,
reservation_status CHAR(40) NOT NULL,
payment_in_advance DOUBLE NOT NULL,
rest_of_payment DOUBLE NOT NULL,
flight_flight_id INTEGER NOT NULL,
reservation_id1 INTEGER NOT NULL
);
ALTER TABLE reservations
ADD CONSTRAINT reservations_pk PRIMARY KEY ( reservation_id,
flight_flight_id,
reservation_id1 );
ALTER TABLE address
ADD CONSTRAINT address_customer_fk FOREIGN KEY ( customer_customer_id )
REFERENCES customer ( customer_id );
ALTER TABLE customer
ADD CONSTRAINT customer_address_fk FOREIGN KEY ( address_country )
REFERENCES address ( country );
ALTER TABLE customer_reservation
ADD CONSTRAINT customer_reservation_fk FOREIGN KEY ( customer_customer_id )
REFERENCES customer ( customer_id );
ALTER TABLE customer_reservation
ADD CONSTRAINT customer_reservation_fk1 FOREIGN KEY ( reservations_reservation_id,
reservations_flight_id1,
reservations_reservation_id1 )
REFERENCES reservations ( reservation_id,
flight_flight_id,
reservation_id1 );
ALTER TABLE fax
ADD CONSTRAINT fax_customer_fk FOREIGN KEY ( customer_customer_id )
REFERENCES customer ( customer_id );
ALTER TABLE flight
ADD CONSTRAINT flight_flight_status_fk FOREIGN KEY ( flight_status_id1 )
REFERENCES flight_status ( flight_status_id );
ALTER TABLE flight_status
ADD CONSTRAINT flight_status_flight_fk FOREIGN KEY ( flight_flight_id )
REFERENCES flight ( flight_id );
ALTER TABLE flight_taxes
ADD CONSTRAINT flight_taxes_reservations_fk FOREIGN KEY (
reservations_reservation_id,
reservations_flight_id1,
reservations_reservation_id1 )
REFERENCES reservations ( reservation_id,
flight_flight_id,
reservation_id1 );
ALTER TABLE phone
ADD CONSTRAINT phone_customer_fk FOREIGN KEY ( customer_customer_id )
REFERENCES customer ( customer_id );
ALTER TABLE reservations
ADD CONSTRAINT reservations_flight_fk FOREIGN KEY ( flight_flight_id )
REFERENCES flight ( flight_id );
Error:
SQL:
ALTER TABLE flight
ADD CONSTRAINT flight_flight_status_fk FOREIGN KEY ( flight_status_id1 )
REFERENCES flight_status ( flight_status_id )
#1005 - Can't create table `aeroplanakiadb`.`flight` (errno: 150 "Foreign key constraint is incorrectly formed")
Any thoughts on how to fix that?