PostgreSQL：如何关联两个表？外键创建报错排查

PostgreSQL要求外键引用的字段必须是被引用表的主键或带有UNIQUE约束的字段。你定义的journeys表中，route_id仅设置了NOT NULL，既不是主键也没有唯一约束，因此routes表的外键无法找到匹配的唯一标识，触发了报错。

结合你的需求（每个Journey对应一组由多个经纬度点组成的Route），这里有两种修正方案：

方案一：保留现有route_id设计，补全约束 #

1. 先给journeys表的route_id添加唯一约束：

ALTER TABLE public.journeys
ADD CONSTRAINT unique_journey_route_id UNIQUE (route_id);

2. 修改routes表的定义，去掉route_id的自动生成逻辑（因为它需要和journeys的route_id一一对应，不能自己生成新的UUID）：

CREATE TABLE public.routes (
  route_id uuid NOT NULL,
  idx smallint NOT NULL,
  date timestamptz NULL,
  latitude real NOT NULL,
  longitude real NOT NULL,  
  CONSTRAINT route_key 
    PRIMARY KEY (route_id, idx),
  CONSTRAINT fk_journeys
    FOREIGN KEY(route_id)
      REFERENCES journeys(route_id)
);

方案二：优化数据模型（更贴合业务逻辑） #

既然每个Journey对应一个专属Route，完全可以用journey_id直接关联，不需要额外的route_id字段，这样模型更简洁：

1. 修正journeys表，确保journey_id为主键，移除多余的route_id：

CREATE TABLE public.journeys (
  journey_id uuid NOT NULL PRIMARY KEY DEFAULT uuid_generate_v4(),
  name text NOT NULL,
  user_id uuid NOT NULL,
  date_created timestamptz NOT NULL,
  date_deleted timestamptz NULL,
  CONSTRAINT fk_users
    FOREIGN KEY(user_id)
      REFERENCES users(user_id)
);

2. 创建routes表，通过journey_id关联到Journey，用idx标识同一路径下的点顺序：

CREATE TABLE public.routes (
  journey_id uuid NOT NULL,
  idx smallint NOT NULL,
  date timestamptz NULL,
  latitude real NOT NULL,
  longitude real NOT NULL,  
  CONSTRAINT route_key 
    PRIMARY KEY (journey_id, idx),
  CONSTRAINT fk_journeys
    FOREIGN KEY(journey_id)
      REFERENCES journeys(journey_id)
);

这种设计更直观，每个经纬度点直接归属到对应的Journey，避免了冗余的route_id字段。

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