странный случай «уникального» ограничения (возможный случай исключения) | Постгрес

У меня есть вопрос относительно ограничения (я думаю, уникального или исключения в данном случае). Я не могу понять этот простой случай:

Например, у меня есть эта упрощенная версия таблицы:

create table user
(
    id                            serial                   not null
    username                      varchar(30)              not null
        constraint user_username_key
            unique,
    is_active                     boolean                  not null,
    company_id                    integer                  not null
       constraint company_user_company_company_company_id
    references company_company
    deferrable initially deferred

Я хотел бы создать такое ограничение, чтобы удовлетворить следующие условия:

Если у нас есть хотя бы одна (или, возможно, несколько записей), ссылающаяся на одно и то же company_id (скажем, = 3), то мы должны (оно должно существовать) иметь одно и только одно имя пользователя, оканчивающееся на «-support» (позволяет произнесите «user1-support») с помощью is_active = True.

Неправильные сценарии:

• we have one or more records but there are no  any username that ends on `‘-support’` amoung this records
• we have one or more records and we have multiple  usernames that ends on `‘-support’` amoung this records

Если у нас есть только одна запись — тогда имя пользователя должно заканчиваться на ‘-support’

Спасибо

Извините, если вопрос наивен

постгрес 11 версия


postgresql sql postgresql-11
person Aleksei Khatkevich    schedule 25.12.2020    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Можно использовать отфильтрованный уникальный индекс:

CREATE UNIQUE INDEX idx ON "user"
  (company_id,(CASE WHEN username LIKE '%-support' THEN 'support' ELSE username END)) 
WHERE is_Active = True;

db‹›демонстрация скрипки

person Lukasz Szozda    schedule 25.12.2020
comment
Спасибо за ответ, но как только вы вставляете первую строку VALUES ('a', True, 3) - это уже нарушает условие: у вас есть 1 или более редордов, ссылающихся на один и тот же company_id, и среди них нет имени пользователя %-поддержки. Вот почему я назвал этот странный уникальный случай ограничения. Мы должны не только следить за тем, чтобы строки не дублировались, но и за тем, чтобы всегда присутствовала одна строка с определенными условиями. - person Aleksei Khatkevich; 26.12.2020
comment
@AlekseiKhatkevich Описанная вами логика означает, что первая активная вставленная запись должна быть типа support. Такое поведение не может быть достигнуто простым ограничением, единственный способ, который я мог придумать, - это триггер или создание функции, которая возвращает логическое значение и добавляет ограничение CHECK (производительность будет ужасной). - person Lukasz Szozda; 26.12.2020