如何在ER图与SQL中实现Flight表与Airport表的双关联？

嘿，这个场景其实挺典型的，我来帮你一步步搞定ER图设计和SQL实现~

要处理Flight和Airport的双向关联，核心是建立两个独立的一对多关系，避免混淆：

• 首先定义Airport实体：它的主键建议用唯一标识（比如IATA机场代码，像PEK、JFK这种3位字符，或者自增的AirportID），这是关联的核心依据。
• 然后定义Flight实体，包含Departure_Airport和Arrival_Airport两个外键字段（对应Airport的主键）。
• 在ER图中，你需要画两条连线：
  • 第一条从Flight指向Airport，标注关系名称为**"Departs From"**（出发自），表示一个航班只能从一个机场出发，而一个机场可以有无数航班从这里起飞。
  • 第二条同样从Flight指向Airport，标注关系名称为**"Arrives At"**（抵达），表示一个航班只能抵达一个机场，一个机场可以接收无数航班。
• 记得给这两个关系明确标注“角色”（比如“出发机场”、“到达机场”），这样看ER图的人一眼就能区分两个关联的含义。

我们用IATA机场代码作为Airport的主键（实际项目中也可以用自增ID，看需求），具体代码如下：

1. 创建Airport表 #

CREATE TABLE Airport (
    AirportCode CHAR(3) PRIMARY KEY, -- 唯一的IATA机场代码
    AirportName VARCHAR(100) NOT NULL,
    City VARCHAR(50) NOT NULL,
    Country VARCHAR(50) NOT NULL
);

2. 创建Flight表（包含两个外键关联） #

CREATE TABLE Flight (
    FlightNumber VARCHAR(10) PRIMARY KEY, -- 比如CA1234、UA890
    Departure_AirportCode CHAR(3) NOT NULL,
    Arrival_AirportCode CHAR(3) NOT NULL,
    DepartureTime DATETIME NOT NULL,
    ArrivalTime DATETIME NOT NULL,
    
    -- 外键约束：关联出发机场
    FOREIGN KEY (Departure_AirportCode)
        REFERENCES Airport(AirportCode)
        ON DELETE RESTRICT -- 避免删除仍有航班关联的机场
        ON UPDATE CASCADE, -- 如果机场代码更新，同步更新航班表
    
    -- 外键约束：关联到达机场
    FOREIGN KEY (Arrival_AirportCode)
        REFERENCES Airport(AirportCode)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

3. 查询关联数据（示例） #

当你需要查询航班的出发/到达机场详细信息时，需要两次关联Airport表，用别名区分：

SELECT
    f.FlightNumber,
    dep.AirportName AS DepartureAirport,
    dep.City AS DepartureCity,
    arr.AirportName AS ArrivalAirport,
    arr.City AS ArrivalCity,
    f.DepartureTime,
    f.ArrivalTime
FROM Flight f
JOIN Airport dep ON f.Departure_AirportCode = dep.AirportCode
JOIN Airport arr ON f.Arrival_AirportCode = arr.AirportCode;

• 不要直接在Flight的Departure_place/Arrival_place字段存城市名，必须关联Airport的主键，这样能避免数据不一致（比如同一个城市有多个机场的情况，比如北京的PEK和PKX）。
• 如果用自增ID作为Airport的主键，Flight表的外键字段就改成INT类型，逻辑是一样的。

内容的提问来源于stack exchange，提问作者Юлия Нечипорук