travel_agency schema无法添加多趟列车的unique(loc_id)替代方案咨询

核心问题定位 #

你当前设置的单字段UNIQUE(loc_id)约束本身不符合业务逻辑：该约束强制要求「一条线路只能对应一趟列车」，但实际业务中同一条线路本来就允许运行多趟不同车次、不同发车时间的列车，所以这个约束属于设计错误，直接删除即可。

你担心「取消约束后无法识别用户预订的对应列车」完全是多余的：trains表自带全局唯一的自增主键id，每一趟列车都对应唯一的trains.id，用户预订表只要关联trains.id即可唯一确定对应的车次，不会出现识别混淆的问题。

优化方案 #

1. 基础调整（必做） #

直接删除trains表的UNIQUE(loc_id)约束即可解决同线路无法添加多趟列车的问题，现有业务逻辑不需要做其他大的改动。

2. 可选优化（避免重复录入冗余车次） #

如果需要避免同一个线路、同一个车次名称、同一个发车时间的列车重复录入，可以新增联合唯一约束，将loc_id、train_name、on_date三个字段绑定为唯一校验规则，既满足同线路多趟列车的录入需求，又能避免无效重复数据。

修改后的SQL示例 #

-- locations表调整：主键本身自带唯一约束，删除多余的UNIQUE(id)声明
CREATE TABLE locations(
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  s_location VARCHAR(100) NOT NULL,
  d_location VARCHAR(100) NOT NULL
);
INSERT INTO
  locations(id, s_location, d_location)
VALUES
  (1, 'delhi','mumbai'),
  (2, 'mumbai','kolkata');

-- trains表调整：删除单字段loc_id的唯一约束，新增联合唯一约束（不需要的话可以删掉下面的UNIQUE行）
CREATE TABLE trains(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    train_name VARCHAR(100) NOT NULL,
    loc_id INTEGER, 
    on_date TIMESTAMP,
    FOREIGN KEY(loc_id) REFERENCES locations(id),
    -- 联合唯一约束，按需保留，不需要可以直接删除该行
    UNIQUE KEY uk_train_route (loc_id, train_name, on_date)
);
INSERT INTO
  trains(train_name, loc_id, on_date)
VALUES
  ('Rajdhani Express', 1, '2021-12-10 12:00:00'),
  ('Asr Ltt Express', 2, '2021-12-9 13:00:00'),
  ('Avadh Express', 1, '2021-12-14 20:00:00');

执行上述SQL后三条测试车次都可以正常插入，且不会出现重复车次的问题。

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