PostgreSQL多列枚举值（empty除外）唯一：用CHECK约束还是触发器？

嘿，这个需求完全可以用CHECK约束来实现，不需要编写触发器函数——PostgreSQL的数组和集合操作刚好能帮我们搞定这个逻辑，而且写法相当简洁。

核心思路 #

我们需要把三个列的取值打包成数组，先过滤掉所有'empty'值，然后检查剩余的非empty值是否都是唯一的。如果存在重复的非empty值，那么过滤后的数组长度会大于去重后的数组长度，反之则相等。

PostgreSQL 13+ 版本的简洁写法 #

PostgreSQL 13及以上版本支持array_unique()函数，可以直接对数组去重，约束写法如下：

CREATE TABLE some_table (
    column1 vals NOT NULL,
    column2 vals NOT NULL,
    column3 vals NOT NULL,
    CONSTRAINT some_table_unique_non_empty_vals_check 
    CHECK (
        array_length(array_remove(ARRAY[column1, column2, column3], 'empty'), 1) = 
        array_length(array_unique(array_remove(ARRAY[column1, column2, column3], 'empty')), 1)
    )
);

兼容旧版本（PostgreSQL <13）的写法 #

如果你的PostgreSQL版本低于13，没有array_unique()函数，可以用unnest()配合聚合函数来实现相同逻辑：

CREATE TABLE some_table (
    column1 vals NOT NULL,
    column2 vals NOT NULL,
    column3 vals NOT NULL,
    CONSTRAINT some_table_unique_non_empty_vals_check 
    CHECK (
        (SELECT count(*) FROM unnest(ARRAY[column1, column2, column3]) AS val WHERE val != 'empty') = 
        (SELECT count(DISTINCT val) FROM unnest(ARRAY[column1, column2, column3]) AS val WHERE val != 'empty')
    )
);

验证你的示例场景 #

我们用你给出的例子来验证约束是否生效：

• 有效组合1：column1: val1、column2: val2、column3: val4 → 过滤后数组为['val1','val2','val4']，去重后长度仍为3，约束通过
• 有效组合2：column1: val1、column2: empty、column3: empty → 过滤后数组为['val1']，去重后长度为1，约束通过
• 无效组合1：column1: val1、column2: val3、column3: val3 → 过滤后数组为['val1','val3','val3']，去重后长度为2，与原长度3不相等，约束触发失败
• 无效组合2：column1: val2、column2: empty、column3: val2 → 过滤后数组为['val2','val2']，去重后长度为1，与原长度2不相等，约束触发失败

为什么不用触发器？ #

这种声明式的CHECK约束比触发器更简洁，不需要额外编写函数和触发逻辑，数据库会自动维护这个约束，而且性能上也更高效——触发器需要在数据变更时执行额外的函数，而CHECK约束是在数据校验阶段直接生效。

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