使一行中的所有非空值唯一

Question

我有一个包含以下列的表：id、col1、col2、col3、col4、col5、col6。

约束表示至少有3列被填充（所以最多3个NULs）。（列不按顺序填充，所以可以有col1、col2、col5被填充，col3、col4、col6是NULs）

如何确保当该列不为NULL时，它在此行的其他列中是唯一的？如何确保非空值的组合在所有行中都是唯一的？

我目前添加了以下约束（以确保至少3个非空）：

  ALTER TABLE my_table
    ADD CONSTRAINT my_constraint CHECK (
      (
        (CASE WHEN col1 IS NULL THEN 0 ELSE 1 END) +
        (CASE WHEN col2 IS NULL THEN 0 ELSE 1 END) +
        (CASE WHEN col3 IS NULL THEN 0 ELSE 1 END) +
        (CASE WHEN col4 IS NULL THEN 0 ELSE 1 END) +
        (CASE WHEN col5 IS NULL THEN 0 ELSE 1 END) +
        (CASE WHEN col6 IS NULL THEN 0 ELSE 1 END)
      ) >= 3
    )

Answer 1

这并不是一个有趣的约束，因为所有的NULL值都是空的，但我认为这确实做到了；

alter table my_table add constraint chk_ugly check
     ( (col1 is null or col1 <> all (array_remove(array[col2, col3, col4, col5, col6], null))) and
       (col2 is null or col2 <> all (array_remove(array[col1, col3, col4, col5, col6], null))) and
       (col3 is null or col3 <> all (array_remove(array[col1, col2, col4, col5, col6], null))) and
       (col4 is null or col4 <> all (array_remove(array[col1, col2, col3, col5, col6], null))) and
       (col5 is null or col5 <> all (array_remove(array[col1, col2, col3, col4, col6], null))) and
       (col6 is null or col6 <> all (array_remove(array[col1, col2, col3, col4, col5], null))) 
     )

我确实认为有一种更好的方法来组织数据，即每个col和id一行。那将是一张新桌子。也就是说，很难对强制执行三个价值观加以限制。

注意：您也可以在Postgres中简化现有约束：

ALTER TABLE my_table
    ADD CONSTRAINT my_constraint CHECK (
        cardinality(array_remove(array[col2, col3, col4, col5, col6], null)) >= 3
    )

Answer 2

据我所知，这实际上是三个不同的限制。至少必须存在三个非空值的方法很简单：

ALTER TABLE my_table
   ADD CONSTRAINT my_constraint CHECK ( num_nonnulls(c1, c2, c3, c4, c5, c6) >= 3);

一行的6列中不应存在重复值的要求可以通过创建一个函数来实现：

create or replace function all_unique(p_row my_table)
  returns boolean
as
$$
  select not exists (
    select v
    from (
        values (p_row.c1), (p_row.c2), (p_row.c3), (p_row.c4), (p_row.c5), (p_row.c6)
    ) t(v)
    where v is not null
    group by v
    having count(*) > 1);
$$
language sql
immutable
strict;

如果一行中的所有非空值都是唯一的，则函数返回true。我将其定义为接收完整的行，以便在添加或删除新列时更容易适应。我不确定这是不是最有效或最简单的方法，但我现在想不出其他方法。

这可用于检查约束：

ALTER TABLE my_table
    ADD CONSTRAINT all_unique CHECK ( all_unique(my_table) );

最后一个要求是，跨多行的非空值应该是唯一的，这可以通过一个唯一的索引来实现。但为此，我们需要一个函数，以排序方式创建非空值数组：

create or replace function non_null_values(p_row my_table)
  returns text[]
as
$$
  select array(
    select distinct v
    from (
        values (p_row.c1), (p_row.c2), (p_row.c3), (p_row.c4), (p_row.c5), (p_row.c6)
    ) t(v)
    where v is not null
    order by v);
$$
language sql
immutable
strict;

为什么要分类？因为Postgres认为数组[1,2,3]与数组[3,1,2]不相等（=）。

然后，此函数可用于唯一索引：

create unique index on my_table (non_null_values(my_table));

在线示例