我应该如何构建数据库以避免此问题？

Question

我正在为学校项目创建一个数据库，并且在这样做时遇到了这个问题。

我正在使用MySQL Workbench 8.0.29

这是我关注的数据库结构 database_design

和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;

我遇到了一些问题

这是应用程序抛出我的错误 error_code

Answer 1

要引用VOL中的多列主键，使外键具有相同数量的列。

错误：

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

对：

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