How should I structure my database to avoid this problem?-CodePudding

I was creating a database for a school project and I have this issue while doing it.

I'm using MySQL workbench 8.0.29

This is the database structure i'm following Database_design

And the SQL:

CREATE DATABASE AEROPORTS; 
USE AEROPORTS;

CREATE TABLE PILOTS ( 
IDENTIFICADOR INT,
NOM VARCHAR(15),
COGNOMS VARCHAR(30),
HORES_VOL INT,
PRIMARY KEY (IDENTIFICADOR)
)engine=innodb;

CREATE TABLE AEROPORTS (
NOM VARCHAR(20),
CIUTAT VARCHAR(20),
PRIMARY KEY (NOM)
)engine=innodb;

CREATE TABLE COMPANYIES (
IDENTIFICADOR INT,
NOM VARCHAR(20),
NACIONALITAT VARCHAR(20),
LOGO varbinary(50),
PRIMARY KEY (IDENTIFICADOR)
)engine=innodb;

CREATE TABLE VOLS (
COMPANYIA INT,
NUMERO_VOL INT,
SORTIDA DATETIME,
ARRIBADA DATETIME,
ORIGEN VARCHAR(20),
DESTI VARCHAR(20),
PRIMARY KEY (COMPANYIA, NUMERO_VOL),
FOREIGN KEY (COMPANYIA) REFERENCES COMPANYIES (IDENTIFICADOR),
FOREIGN KEY (DESTI) REFERENCES AEROPORTS (NOM)
)engine=innodb;

CREATE TABLE PASSATGERS (
COMPANYIA INT,
VOL INT,
NOM VARCHAR(15),
COGNOMS VARCHAR(30),
CLASSE VARCHAR(15),
PRIMARY KEY (COMPANYIA, VOL, NOM, COGNOMS),
FOREIGN KEY (COMPANYIA) REFERENCES VOLS (COMPANYIA),
FOREIGN KEY (VOL) REFERENCES VOLS (NUMERO_VOL)
)engine=innodb;

CREATE TABLE PILOTAR (
COMPANYIA INT,
VOL INT,
PILOT INT,
PRIMARY KEY (COMPANYIA, VOL, PILOT),
FOREIGN KEY (COMPANYIA) REFERENCES VOLS (COMPANYIA),
FOREIGN KEY (PILOT) REFERENCES PILOTS (IDENTIFICADOR)
)engine=innodb;

CREATE TABLE AVIONS (
NUMERO_AVIO INT,
HORES_VOLS DATETIME,
PLACES_PRIMERA INT,
PLACES_TURISTA INT,
COMPANYIA INT,
PRIMARY KEY (NUMERO_AVIO),
FOREIGN KEY (COMPANYIA) REFERENCES COMPANYIES (IDENTIFICADOR)
)engine=innodb;

I'm having some problems linking "COMPANYIES" from "PASSATGERS" table

This is the error that the application throws me error_code

CodePudding user response：

To reference a multi-column primary key in VOLS, make the foreign key have the same number of columns.

WRONG:

FOREIGN KEY (COMPANYIA) REFERENCES VOLS (COMPANYIA),
FOREIGN KEY (VOL) REFERENCES VOLS (NUMERO_VOL)

RIGHT:

FOREIGN KEY (COMPANYIA, VOL) REFERENCES VOLS (COMPANYIA, NUMERO_VOL)