PostgreSQL 9.5中如何实现两列及反向组合的唯一性与不等约束

你已经完成了第一个约束，剩下两个其实可以通过CHECK约束和表达式唯一索引来实现，刚好我之前处理过类似的场景，给你详细说下：

约束3：确保id1和id2不相等 #

这个最简单，直接加个CHECK约束就行，它会在插入或更新数据时验证条件：

ALTER TABLE test ADD CONSTRAINT test_id1_not_equal_id2 CHECK (id1 <> id2);

这样像(1,1)这种记录就会被直接拦截。

约束2：禁止(id1,id2)和(id2,id1)的重复 #

普通的UNIQUE(id1,id2)只能管完全一样的组合，没法处理顺序颠倒的情况。这里我们可以用PostgreSQL的LEAST()和GREATEST()函数，把两个值按大小排序后生成一个唯一索引——不管你插入的是(1,2)还是(2,1)，排序后的组合都是(1,2)，这样就能避免这种双向重复：

CREATE UNIQUE INDEX test_id1_id2_bidirectional_unique ON public.test USING btree ((LEAST(id1, id2)), (GREATEST(id1, id2)));

值得一提的是，这个索引其实已经覆盖了你之前加的第一个约束（相同组合不重复），所以如果是从头建表的话，不需要单独加UNIQUE(id1,id2)，用这个表达式索引就够了。

整合后的完整表定义 #

如果是重新创建表，把所有约束放在一起会更清晰，就是你最终得到的这个方案：

CREATE TABLE public.test(
    id bigint NOT NULL DEFAULT nextval('test_id_seq'::regclass),
    id1 integer NOT NULL,
    id2 integer NOT NULL,
    CONSTRAINT test_pkey PRIMARY KEY (id),
    CONSTRAINT test_check CHECK (id1 <> id2)
);

CREATE UNIQUE INDEX test_id1_id2_unique ON public.test USING btree ((LEAST(id1, id2)), (GREATEST(id1, id2)));

这样三个约束就全部生效了，完全符合你预期的校验效果~

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