Блог

Я использую postgresql 10.12

Я пометил объекты. Некоторые из них являются стандартными, некоторые нет. Стандартные объекты являются общими для всех пользователей, тогда как нестандартные объекты принадлежат пользователю. Итак, допустим, у меня есть таблица Entity с текстовым столбцом Label и столбцом user_id , который равен null для стандартных объектов.

 CREATE TABLE Entity
(
  id uuid NOT NULL PRIMARY KEY,
  user_id integer,
  label text NOT NULL,
)
  

Вот мое ограничение: две нестандартные сущности, принадлежащие разным пользователям, могут иметь одну и ту же метку. Стандартные метки объектов уникальны, а объекты данных пользователей имеют уникальные метки. Сложная часть: метка должна быть уникальной в группе стандартных объектов объектов данного пользователя.

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

 __table_args__ = (
    UniqueConstraint("label", "user_id", name="_entity_label_user_uc"),
    db.Index(
        "_entity_standard_label_uc",
        label,
        user_id.is_(None),
        unique=True,
        postgresql_where=(user_id.is_(None)),
    ),
)
  

Моя проблема с этим ограничением заключается в том, что я не гарантирую, что у объекта user не будет стандартной метки объекта.

Пример:

  ---- --------- ------------ 
| id | user_id |   label    |
 ---- --------- ------------ 
|  1 | null    | std_ent    |
|  2 | 42      | user_ent_1 |
|  3 | 42      | user_ent_2 |
|  4 | 43      | user_ent_1 |
 ---- --------- ------------ 
  

Это допустимая таблица. Я хочу убедиться, что больше невозможно создать объект с меткой std_ent , что пользователь 42 не может создать другой объект с меткой user_ent_1 или user_ent_2 и что пользователь 43 не может создать другой объект с меткой user_ent_1 .

С моими текущими ограничениями для пользователей 42 и 43 все еще возможно создать объект с меткой std_ent , что я и хочу исправить.

Есть идея?

Если ваши уникальные ограничения выполняют свою работу по предотвращению ввода пользователями повторяющихся меток для своих собственных «пользовательских объектов», то вы можете запретить им вводить метку «стандартного объекта», добавив триггер.

Вы создаете функцию …

 CREATE OR REPLACE FUNCTION public.std_label_check()
    RETURNS trigger
    LANGUAGE plpgsql
AS $function$
    begin
        if exists(
                select * from entity 
                where label = new.label and user_id is null) then
            raise exception '"%" is already a standard entity', new.label;
        end if;
        return new;
    end;
$function$
;
  

… а затем прикрепите его в качестве триггера к таблице

 CREATE TRIGGER entity_std_label_check
BEFORE INSERT 
ON public.entity FOR EACH ROW
EXECUTE PROCEDURE std_label_check()