PostgreSQL无需触发器实现带条件的多列唯一约束

核心需求 #

在PostgreSQL中，不依赖触发器的前提下，能否实现以下带条件的多列约束逻辑：

• 若已存在a、c值确定且b为null的记录，则无法插入相同a、c组合的记录；
• 若已存在a、b、c均为具体值的记录，则无法插入相同a、c组合且b为null的记录，但允许插入b值不同的变体。

基础示例表 #

create table t (
    id serial primary key,
    a int not null,
    b int default null,
    c int not null
);

业务场景实例 #

业务表结构 #

create table if not exists booking (
    id serial4,
    media_id int4 not null,
    data_access int2 not null default session_user_id(),
    product_line text not null,
    product_variant text default null,
    area_nr int2 not null,
    net int2 not null,
    period int2range not null,
        -- 若起止周数一致，则仅预订单周
    entry_time timestamp not null default now(),

    constraint "internally used surrogate key to a booking" primary key (id),

    constraint "no overlapping booking periods of a distributer" exclude using gist (
        data_access with =,
        product_line with =,
        area_nr with =,
        net with =,
        period with &&
    ) where (product_variant is null),

    constraint "no overlapping booking period of a sub-distributer" exclude using gist (
        data_access with =,
        product_line with =,
        product_variant with =,
        area_nr with =,
        net with =,
        period with &&
    ),
    /*
    constraint "known booked product"
        foreign key (data_access, product_line, product_variant)
            references product(data_access, line_abbr, variant_abbr),

    constraint "known booked area"
        foreign key (data_access, area_nr) references area(data_access, nr),

    constraint "known booked net"
        foreign key (data_access, product_line, net)
            references net(data_access, product_line, nr),

    constraint "known media of a booking"
        foreign key (media_id) references media(id) on delete cascade,

    */
    constraint "closed booking periods of areas" check (
        upper_inc(period)
    )
);

注：已注释外键以简化理解

冲突校验场景 #

场景1：已有分销商全量预订，禁止子分销商预订

执行以下插入时，第二条插入应报错：

insert into booking
    (product_line, product_variant, area_nr, net, period, media_id)
values
    ('CB', null, 1, 4, '[2239, 2245]', 5);

insert into booking
    (product_line, product_variant, area_nr, net, period, media_id)
values
    ('CB', 'P', 1, 4, '[2239, 2245]', 5);

原因：该时段已存在整个分销商的预订记录，不允许再插入同条件下的子分销商预订

场景2：已有子分销商预订，禁止分销商全量预订

执行以下插入时，第三条插入应报错：

insert into booking
    (product_line, product_variant, area_nr, net, period, media_id)
values
    ('CB', 'F', 2, 6, '[2230, 2245]', 5);

insert into booking
    (product_line, product_variant, area_nr, net, period, media_id)
values
    ('CB', 'P', 2, 6, '[2230, 2245]', 5);

insert into booking
    (product_line, product_variant, area_nr, net, period, media_id)
values
    ('CB', null, 2, 6, '[2230, 2245]', 5);

原因：该时段已有子分销商的预订记录，无法再插入整个分销商的预订

当前触发器实现方案 #

目前通过以下触发器完成冲突校验，现寻求无触发器的替代方案：

create or replace function _avoid_incomplete_distributers() returns trigger as $$ begin
    if exists (select from booking where 
        product_line = new.product_line and case
            when new.product_variant is null then product_variant is not null
            when new.product_variant is not null then product_variant is null
        end and
        area_nr = new.area_nr and
        net = new.net and
        period = new.period
    ) then
        raise exception 'collision of distributer % with related sub-distributers for area % and net % in period %', new.product_line, new.area_nr, new.net, new.period;
    else
        return new;
    end if;
end $$ language plpgsql;

create trigger before_insert_incomplete_distributer before insert on booking for each row
    execute procedure _avoid_incomplete_distributers();

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