我有Postgresql表
id ColA ColB ------------------ 1 'a' 'b' 2 'c' 'd'
我想让ColA和ColB中的值在两列中都是唯一的,即任何这些插入都是禁止的：
INSERT INTO table (ColA,ColB) values('a','e');
INSERT INTO table (ColA,ColB) values('z','a');
INSERT INTO table (ColA,ColB) values('d','g');
并允许任何这些插入：
INSERT INTO table (ColA,ColB) values('l','k');
所以
CONSTRAINT unique_name UNIQUE (ColA,ColB)
不适合,因为它将允许以前的4个插入中的任何一个.
遗憾的是,使用简单独特的约束/索引(如果可以用它们解决它)就无法轻易解决这个问题.
你需要的是exclusion constraint：基于碰撞之类的东西排除某些行的能力.唯一约束只是特定的排除约束(它们基于相等冲突).
所以,从理论上讲,你只需要排除每个row1,其中已经有一个row2,这个表达式为真：ARRAY [row1.cola,row1.colb]&& ARRAY [row2.cola,row2.colb]
该索引可以完成这项工作(目前只有gist索引支持排除约束)：
ALTER TABLE table_name ADD CONSTRAINT table_name_exclusion EXCLUDE USING gist ((ARRAY[cola,colb]) WITH &&);
但不幸的是,数组没有默认的运算符类(使用gist).有一个intarray module,它只提供一个整数数组,但没有提供文本数组.
如果你真的想要解决这个问题,你总是可以滥用range types(f.ex.我使用了相邻的| – – 运算符,它处理所有情况,无法用唯一处理)…
-- there is no built-in type for text ranges neither,-- but it can can be created fairly easily:
CREATE TYPE textrange AS RANGE (
SUBTYPE = text
);
ALTER TABLE table_name
ADD CONSTRAINT table_name_exclusion
EXCLUDE USING gist ((textrange(least(cola,colb),greatest(cola,colb))) WITH -|-);
-- the exclusion constraint above does not handle all situations:
ALTER TABLE table_name
ADD CONSTRAINT table_name_check
CHECK (cola is distinct from colb); -- without this,empty ranges could be created,-- which are not adjacent to any other range
CREATE UNIQUE INDEX table_name_unique
ON table_name ((ARRAY[least(cola,colb)]));
-- without this,duplicated rows could be created,-- because ranges are not adjacent to themselves
