MySQL 8.0创建机场数据库PASSATGERS表外键报错如何调整结构

使用MySQL Workbench 8.0.29开发学校项目的机场管理数据库时，创建乘客表（PASSATGERS）关联航司、航班相关字段时触发外键报错，初始建表语句如下：

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;

InnoDB引擎要求外键必须引用被关联表的完整主键，或者具备唯一约束的字段/字段组。初始建表语句存在3个核心问题：

1. 航班表VOLS的主键是(COMPANYIA, NUMERO_VOL)联合主键，单独的COMPANYIA、NUMERO_VOL字段都不具备唯一约束（不同航司可以使用相同的航班号），但PASSATGERS、PILOTAR表把联合主键拆成了两个独立外键分别关联单个字段，不符合InnoDB外键创建规则，直接触发报错。
2. 航班表VOLS的出发地ORIGEN字段缺失外键约束，没有和机场表AEROPORTS做关联，存在数据一致性风险。
3. 飞机表AVIONS的HORES_VOLS（累计飞行时长）字段错误使用DATETIME类型，该字段是数值型的时长统计，应该使用INT类型存储小时数。

按照外键规则调整关联逻辑，修正字段类型错误后的完整SQL如下：

CREATE DATABASE IF NOT EXISTS AEROPORTS; 
USE AEROPORTS;

-- 飞行员表
CREATE TABLE PILOTS ( 
    IDENTIFICADOR INT PRIMARY KEY,
    NOM VARCHAR(15) NOT NULL,
    COGNOMS VARCHAR(30) NOT NULL,
    HORES_VOL INT DEFAULT 0
) ENGINE=InnoDB;

-- 机场表
CREATE TABLE AEROPORTS (
    NOM VARCHAR(20) PRIMARY KEY,
    CIUTAT VARCHAR(20) NOT NULL
) ENGINE=InnoDB;

-- 航司表
CREATE TABLE COMPANYIES (
    IDENTIFICADOR INT PRIMARY KEY,
    NOM VARCHAR(20) NOT NULL,
    NACIONALITAT VARCHAR(20) NOT NULL,
    LOGO VARBINARY(50)
) ENGINE=InnoDB;

-- 航班表
CREATE TABLE VOLS (
    COMPANYIA INT NOT NULL,
    NUMERO_VOL INT NOT NULL,
    SORTIDA DATETIME NOT NULL,
    ARRIBADA DATETIME NOT NULL,
    ORIGEN VARCHAR(20) NOT NULL,
    DESTI VARCHAR(20) NOT NULL,
    PRIMARY KEY (COMPANYIA, NUMERO_VOL),
    FOREIGN KEY (COMPANYIA) REFERENCES COMPANYIES(IDENTIFICADOR),
    FOREIGN KEY (ORIGEN) REFERENCES AEROPORTS(NOM),
    FOREIGN KEY (DESTI) REFERENCES AEROPORTS(NOM)
) ENGINE=InnoDB;

-- 乘客表
CREATE TABLE PASSATGERS (
    COMPANYIA INT NOT NULL,
    VOL INT NOT NULL,
    NOM VARCHAR(15) NOT NULL,
    COGNOMS VARCHAR(30) NOT NULL,
    CLASSE VARCHAR(15) NOT NULL,
    PRIMARY KEY (COMPANYIA, VOL, NOM, COGNOMS),
    -- 创建联合外键，关联VOLS的完整联合主键
    FOREIGN KEY (COMPANYIA, VOL) REFERENCES VOLS(COMPANYIA, NUMERO_VOL)
) ENGINE=InnoDB;

-- 飞行员执飞关系表
CREATE TABLE PILOTAR (
    COMPANYIA INT NOT NULL,
    VOL INT NOT NULL,
    PILOT INT NOT NULL,
    PRIMARY KEY (COMPANYIA, VOL, PILOT),
    -- 同样使用联合外键关联VOLS主键
    FOREIGN KEY (COMPANYIA, VOL) REFERENCES VOLS(COMPANYIA, NUMERO_VOL),
    FOREIGN KEY (PILOT) REFERENCES PILOTS(IDENTIFICADOR)
) ENGINE=InnoDB;

-- 飞机表
CREATE TABLE AVIONS (
    NUMERO_AVIO INT PRIMARY KEY,
    HORES_VOLS INT DEFAULT 0, -- 修正字段类型为INT存储飞行小时数
    PLACES_PRIMERA INT NOT NULL,
    PLACES_TURISTA INT NOT NULL,
    COMPANYIA INT NOT NULL,
    FOREIGN KEY (COMPANYIA) REFERENCES COMPANYIES(IDENTIFICADOR)
) ENGINE=InnoDB;

• 所有非空字段添加NOT NULL约束，避免无效空值进入业务表
• 数值类统计字段设置默认值（比如飞行小时数默认0），减少业务层赋值逻辑
• 可以根据查询需求给常用查询字段（比如航班的出发到达时间、乘客姓名）添加普通索引，提升查询效率

内容的提问来源于stack exchange，提问作者GUAYABITO