带条件的多列唯一约束

Question

基于此示例：

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

在PostgreSQL中，有没有一种方法可以在不使用触发器的情况下引入一个唯一的约束，即如果已经有一个记录具有a和c的值，而b为空，则不能在a和c的相同组合中插入其他记录。如果a、b和c已经有一个具有具体值的数据集，则在b为空的情况下，可能永远不会插入具有相同a和c的记录，只有b具有不同值的其他变体。

示例：

(5，空，1(

(4，6，9(

(4，7，9(

(5，空，2(

(5，3，1(--拒绝/错误

(4，无效，9(-也拒绝

--

下面是我的具体用例来更好地说明这个问题：

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,
-- If the start and end calendar weeks match, only one week is booked.
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)
)
);

我把外键注释掉了，只是为了更好地理解才把它们留在里面。

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);

最后一次插入应该会导致一个错误，因为整个分销商已经在给定的时间段内被预订。

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();

Answer 1

您可以在不使用触发器的情况下使用编写自己的检查函数并添加到表检查约束来完成此操作。Fox示例：

这是函数示例：

CREATE OR REPLACE FUNCTION check_booking(v_pline text, v_pvar text, v_area int2)
RETURNS bool
LANGUAGE plpgsql
AS $function$
BEGIN
if (v_pvar is null) then 
if (exists(select 1 from booking where product_line = v_pline and area_nr = v_area)) then 
return false;
end if;
end if; 

if (v_pvar is not null) then 
if (exists(select 1 from booking where product_line = v_pline and area_nr = v_area and product_variant is null)) then 
return false;
end if;
end if; 

return true;

END;
$function$   

这是添加约束示例：

ALTER TABLE booking 
ADD CONSTRAINT booking_check_constraint 
CHECK (check_booking(product_line, product_variant, area_nr));