为何关联Customer与Orders表时出现唯一约束不匹配报错？

问题描述 #

尝试通过cust_id字段关联Customer表与Orders表时，执行操作触发如下错误：

there is no unique constraint matching given keys for referenced table "Orders"

涉及的核心建表及约束语句如下：

CREATE TABLE "Orders" (
"row_id" INT PRIMARY KEY,
"order_id" VARCHAR(20) NOT NULL,
"created_at" DATE NOT NULL,
"item_id" VARCHAR(10) NOT NULL,
"quantity" INT NOT NULL,
"cust_id" INT NOT NULL,
"delivery" BOOLEAN NOT NULL,
"add_id" INT NOT NULL
);

CREATE TABLE "Customer" (
"cust_id" int NOT NULL,
"cust_firstname" varchar(50) NOT NULL,
"cust_latname" varchar(50) NOT NULL,
CONSTRAINT "pk_Customer" PRIMARY KEY (
"cust_id"
)
);

-- 错误的外键约束
ALTER TABLE "Customer" ADD CONSTRAINT "fk_Customer_cust_id" FOREIGN KEY("cust_id")
REFERENCES "Orders" ("cust_id");

报错原因 #

1. 关联方向完全错误：
   业务逻辑上，一个客户可拥有多个订单，正确的关联应该是Orders表的cust_id作为外键，引用Customer表的主键cust_id（Customer.cust_id已设为主键，自带唯一约束）。你当前的操作是让Customer.cust_id反向引用Orders.cust_id，完全违背业务逻辑。
2. 被引用字段无唯一约束：
   Orders表的cust_id既不是主键，也未添加唯一约束，而PostgreSQL要求外键引用的字段必须具备唯一约束（主键或唯一索引），否则无法保证数据一致性，因此触发报错。

修复步骤 #

1. 删除错误的外键约束 #

执行SQL删除反向关联的错误约束：

ALTER TABLE "Customer" DROP CONSTRAINT "fk_Customer_cust_id";

2. 创建正确的外键关联 #

让Orders表的cust_id引用Customer表的主键cust_id：

ALTER TABLE "Orders" ADD CONSTRAINT "fk_Orders_cust_id" FOREIGN KEY("cust_id")
REFERENCES "Customer" ("cust_id");

额外注意事项 #

检查其他约束语句还存在类似问题，建议一并修正：

• ALTER TABLE "ingredient" ADD CONSTRAINT "fk_ingredient_ing_id" FOREIGN KEY("ing_id") REFERENCES "recipe" ("ing_id");：recipe.ing_id无唯一约束，且关联方向错误，应改为recipe.ing_id引用ingredient.ing_id
• ALTER TABLE "recipe" ADD CONSTRAINT "fk_recipe_recipe_id" FOREIGN KEY("recipe_id") REFERENCES "item" ("sku");：item.sku无唯一约束，需先给item.sku添加唯一约束，再建立外键
• ALTER TABLE "staff" ADD CONSTRAINT "fk_staff_staff_id" FOREIGN KEY("staff_id") REFERENCES "rota" ("staff_id");：关联方向错误，应改为rota.staff_id引用staff.staff_id

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