Postgres执行建表SQL报错引用表无匹配唯一约束如何解决？

表结构参考示意图：

你导入时触发的报错如下：

error: migration failed: there is no unique constraint matching given keys for referenced table "CreatedAt" in line 0: CREATE TABLE "CreatedAt" (

最初报错的原因 #

PostgreSQL 外键约束强制要求：被引用的列必须是被引用表的唯一键（主键或带UNIQUE约束的列）。
你最初的脚本存在两个核心问题：

• CreatedAt 表只有 id 是主键（唯一），你用 Intersection.intersection_id 引用 CreatedAt.intersection_id、trafficData.trafficData_id 引用 CreatedAt.trafficData_id，但这两个被引用的列都没有加唯一约束，直接触发报错。
• 外键方向完全设计反了：从业务逻辑看，CreatedAt 是通用的创建时间记录表，应该是它的字段关联业务表的主键，而不是业务表反过来关联它的非主键字段。

修改后脚本仍报错的问题梳理 #

你调整后的脚本除了上述外键方向、唯一约束的问题外，还新增了语法和逻辑错误：

• 语法错误：createdAt 表的 intersection_id int, 末尾多了逗号；PropertyReference 表重复定义了 id 列，且倒数第二个 id int 末尾缺少逗号，语法不合法
• 外键逻辑完全错误：用 Intersection.id（主键）引用 createdAt.intersection_id（非唯一普通列），不仅关联逻辑反向，也不符合唯一约束要求；trafficData 的外键错误引用了 createdAt.intersection_id，和自身业务属性完全不匹配；PropertyReference、StatisticsTraffic 的外键方向全部反向，且引用的都是目标表的非唯一列
• 字段设计冗余冲突：PropertyReference 同时定义了 id SERIAL PRIMARY KEY 和普通列 id int，字段重复会直接报错

修正后的正确建表方案 #

先梳理符合你Golang结构体的关联逻辑：

• 每个trafficData对应1条StatisticsTraffic统计数据、1条PropertyReference属性数据
• CreatedAt表统一存储所有业务数据的创建时间，和业务表1:1关联
• 所有外键都指向目标表的主键（自带唯一约束，符合要求）

修正后的脚本如下：

-- 通用创建时间表
CREATE TABLE "CreatedAt" (
    "id" SERIAL PRIMARY KEY,
    "created" timestamptz NOT NULL DEFAULT now(),
    "traffic_data_id" INT UNIQUE,
    "intersection_id" INT UNIQUE
);

-- 路口表
CREATE TABLE "Intersection" (
    "id" SERIAL PRIMARY KEY,
    "site_no" INT,
    "latitude" FLOAT8,
    "longitude" FLOAT8,
    "site_name" VARCHAR,
    "relationships" VARCHAR,
    "type" VARCHAR,
    "op_status" VARCHAR,
    "road_classification" VARCHAR,
    "road_geometry" VARCHAR,
    "road_layout" VARCHAR,
    "suburb" VARCHAR,
    "switch_on_date" TIMESTAMP
);

-- 交通统计数据表
CREATE TABLE "StatisticsTraffic" (
    "id" SERIAL PRIMARY KEY,
    "interval_start" TIMESTAMP,
    "travel_time" INT,
    "delay" INT,
    "speed" INT,
    "excess_delay" INT,
    "congestion" INT,
    "score" INT,
    "flow_restriction_score" INT,
    "average_density" INT,
    "density" INT,
    "enough_data" BOOL,
    "ignored" BOOL,
    "closed" BOOL
);

-- 属性引用表
CREATE TABLE "PropertyReference" (
    "id" SERIAL PRIMARY KEY,
    "name" VARCHAR,
    "href" VARCHAR
);

-- 交通数据表
CREATE TABLE "trafficData" (
    "id" SERIAL PRIMARY KEY,
    "properties_id" INT UNIQUE,
    "latest_stats_id" INT UNIQUE,
    "name" VARCHAR,
    "length" INT,
    "min_number_of_lanes" INT8,
    "minimum_tt" INT,
    "is_freeway" BOOL,
    "direction" VARCHAR
);

-- 补充外键约束
ALTER TABLE "CreatedAt"
    ADD CONSTRAINT fk_createdat_intersection FOREIGN KEY ("intersection_id") REFERENCES "Intersection"("id"),
    ADD CONSTRAINT fk_createdat_trafficdata FOREIGN KEY ("traffic_data_id") REFERENCES "trafficData"("id");

ALTER TABLE "trafficData"
    ADD CONSTRAINT fk_trafficdata_property FOREIGN KEY ("properties_id") REFERENCES "PropertyReference"("id"),
    ADD CONSTRAINT fk_trafficdata_stats FOREIGN KEY ("latest_stats_id") REFERENCES "StatisticsTraffic"("id");

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