Multi-column unique constraints with condition

Question

Based on this example:

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

Is there a way in PostgreSQL, without resorting to triggers, to incorporate a unique constraint that if there is already a record with values for a and c while b is null, no further records may be inserted in the same combination of a and c. The same should applied the other way round, if there is already a data set for a, b and c with concrete values, that a record with the same a and c may never be inserted where b is null, only further variants with different values for b.

examples:

(5, null, 1)

(4, 6, 9)

(4, 7, 9)

(5, null, 2)

(5, 3, 1) -- refused / error

(4, null, 9) -- also refused

—

Here is my specific use case to better illustrate the problem:

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

I commented out the foreign keys and only left them in for better understanding.

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

The last insert should result in an error since the entire distributor has already been booked for the given period.

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

The last one should give an error too since one or more sub-distributors have already been booked, so the entire distributor may no longer be available.

The problem: You first have to check whether a distributor or sub-distributor has already been booked in the same period / range for the same area, net and products, which I solve using a trigger. (due to the cool range feature and related constraints, I was already able to very elegantly exclude overlapping booking periods without a trigger):

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

Why no triggers? Any solution would be like a trigger any way. — Bohemian, Sep 03 '22 at 18:29

score 2 · Answer 1 · answered Sep 03 '22 at 18:20

2

You can do this without triggers using write your own check function and adding to table check constraint. Fox example:

This is function sample:

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$

This is add constraint sample:

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

answered Sep 03 '22 at 18:20

Ramin Faracov

3,032
1
2
8

Or more simply just `return (not exists(select 1 from booking where product_line = v_pline and area_nr = v_area));` etc – Bohemian Sep 03 '22 at 18:25
Isn't it recommended to only use immutable functions in check-constraints, i.e. that don't read any data sets? And what would be the advantage over a trigger? – Gedankenpolizei Sep 04 '22 at 23:41
@Gedankenpolizei you are right, you can change my function type to immutable, no problem. But for about check constraints, the rule of thumb is to use CHECK constraint when possible. A CHECK constraint is faster, simpler, more portable, needs less code and is less error prone. For reloading a dump process you can use the NOT VALID modifier as middle ground and, optionally, VALIDATE it later. But triggers can easily be circumvented by other triggers, for instance. A TRIGGER is more complicated. Use it for more complex requirements. But I think that both methods will be gets same performance – Ramin Faracov Sep 05 '22 at 15:54
https://stackoverflow.com/questions/10179121/sql-sub-queries-in-check-constraint according to that, check constraints should be limited to the values of the current row / be done in isolation. I guess I'll stick with my trigger if this complex null condition can't be implemented as an alternative unique constraint. – Gedankenpolizei Sep 05 '22 at 17:48

Multi-column unique constraints with condition

1 Answers1