PostgreSQL中为自引用关联表添加唯一约束的实现方案

a：可以通过CONSTRAINT实现需求 #

你可以利用PostgreSQL的LEAST()和GREATEST()函数，结合唯一约束来实现这个需求。核心思路是让关联的两个食材ID始终以固定顺序（比如较小的ID在前、较大的ID在后）存储，这样无论插入的是(盐ID, 胡椒ID)还是(胡椒ID, 盐ID)，都会被统一转换成相同的有序组合，唯一约束就能直接阻止重复插入。

示例代码如下：
假设你的关联表名为ingredient_relations，包含ingredient_id1和ingredient_id2两个字段：

-- 创建表时直接添加约束
CREATE TABLE ingredient_relations (
    ingredient_id1 INT REFERENCES ingredients(id),
    ingredient_id2 INT REFERENCES ingredients(id),
    -- 可选：阻止食材自关联
    CHECK (ingredient_id1 != ingredient_id2),
    -- 基于有序ID组合的唯一约束
    UNIQUE (LEAST(ingredient_id1, ingredient_id2), GREATEST(ingredient_id1, ingredient_id2))
);

-- 若表已存在，单独添加约束
ALTER TABLE ingredient_relations
ADD CONSTRAINT unique_ingredient_pair
UNIQUE (LEAST(ingredient_id1, ingredient_id2), GREATEST(ingredient_id1, ingredient_id2));

这个方案完全依赖数据库原生约束机制，无需触发器，性能和可靠性都更优，完全匹配你的需求。

b：CONSTRAINT是最优方案，触发器仅为特殊场景备选 #

既然通过CONSTRAINT就能实现需求，这就是最直接高效的方案。如果因为业务特殊要求必须保留原始ID顺序，触发器可以作为替代，但它逻辑更复杂，还会带来额外的性能开销，远不如约束方案简洁可靠。

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