多个列，每个值在所有(每个)列中都是唯一的;由1个事务处理

Question

我正在Postgresql中搜索这样一个方法，即多个列中的每一列中的每个值在所有(每个(列中都是唯一的。

示例，有2列：

col_1  col_2
--------------
a      b         # ok
c      d         # ok
e                # ok
f      a         # forbidden
b                # forbidden
b         # forbidden

我需要这些列中的每一个写入都由一个事务处理，特别是(对于某些行(：

copy col_2 in col_1 and delete col_2

有人知道吗？

Answer 1

这可能应该是一个注释，但它太长了，我无法格式化那里的代码示例
您不能获得唯一约束，也不能跨多列进行索引。你可能可以使用触发器，但即使在那里也不简单：

create or replace function unique_over_2col() 
returns trigger 
language plpgsql
as $$
begin 
if exists ( select null 
from test 
where new.col_1 = col_1
or new.col_1 = col_2 
or new.col_2 = col_1 
or new.col_2 = col_2
)
then 
return null; 
else 
return new;
end if; 
end; 
$$; 
create trigger test_biur
before insert or update 
on <your table name here> 
for each row 
execute function unique_over_2col();

您的触发器必须将每个新列与每个现有列进行比较。以上只是针对您提到的2列进行了比较，从而得出了4个比较结果。您的多个列将极大地扩展这一点。我将重复@Bergi的建议规范化您的模式。

BTW：请解释一下copy col_2 in col_1 and delete col_2，它完全没有意义。也许最好解释一下你所面临的业务问题，而不是如何解决它。

Answer 2

一个有点丑陋但有效的解决方案：

CREATE TABLE tablename (col1 integer, col2 integer);
CREATE OR REPLACE FUNCTION pr_tablename_insertuniqueonly()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
v_new_values integer[] =  ARRAY[NEW.col1, NEW.col2];
BEGIN
IF (NEW.col1=NEW.col2) THEN
RETURN null;
END IF;
IF EXISTS(SELECT 1 FROM tablename t WHERE t.col1 = ANY(v_new_values) OR t.col2 = ANY(v_new_values)) THEN
RETURN null;
ELSE
RETURN NEW;
END IF;
RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER tr_iine_tablename BEFORE INSERT ON tablename FOR EACH ROW EXECUTE PROCEDURE pr_tablename_insertuniqueonly();

stack=#插入表名值(1,1(
INSERT 0
stack=#插入表名值(1,2(
INSERT 0 1
stack=#插入表名值(3,2(
INSERT 0
stack=#插入表名值(3,1(
INSERT 0
stack=#插入表名值(3,4(
INSERT 0 1
stack=#select*from tablename
col1|col2
1|2
3|4
(2行(