sql - 对不能同时为空的多列的唯一约束

Question

我想问一下在 Postgres 10 中是否有更好的方法来模拟以下行为:

CREATE TABLE test.my_table
(
    id   UUID PRIMARY KEY,
    id_a UUID,
    id_b UUID,
    some_shared_data JSONB,
    UNIQUE (id_a, id_b)
);

CREATE UNIQUE INDEX IF NOT EXISTS b_null_constraint ON test.my_table (id_a) WHERE id_b IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS a_null_constraint ON test.my_table (id_b) WHERE id_a IS NULL;

ALTER TABLE test.my_table
    ADD CONSTRAINT both_null_constraint CHECK (
        (id_b IS NOT NULL) OR (id_a IS NOT NULL));

即约束是:

1. id_a 和id_b 都不能为null
2. id_a 和id_b 的组合必须是唯一的(包括其中一个为null 的情况)

我觉得上面设置这个的代码不是很有表现力。人们会以另一种/更规范化的方式做到这一点吗？我尝试将其拆分到单独的表中，但约束 (1.) 很难满足。

Answer 1

只需两个 unique 约束就可以做到这一点。第二个是:

CREATE UNIQUE INDEX IF NOT EXISTS ab_null_constraint ON my_table ( coalesce(id_a, id_b), (id_a is null) WHERE id_a IS NULL or id_b is null;

Here是一个数据库<> fiddle 。

实际上，您可以将所有这些组合成一个唯一索引:

CREATE UNIQUE INDEX IF NOT EXISTS ab_null_constraint ON
     my_table ( coalesce(id_a, id_b),
                coalesce(id_b, id_a),
                (id_a is null),
                (id_b is null)
              );

Here是一个 db<>fiddle for this.

您可能会发现您的原始公式更易于维护。