nvv 1 апр 2023 в 20:51

Дублирование данных для создания ограничений (контролей) на уровне БД

Средний

5 мин

3.2K

PostgreSQL*SQL*Администрирование баз данных*

Кейс

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

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

Исходная типовая задача

есть пользователи, группы и роли;
пользователи могут входить в группы с указанием роли;
пользователи не должны входить в группу с одной и той же ролью несколько раз;
для некоторых ролей в группе может быть не более одного пользователя.

Это могут быть:

игроки, команды и роли в команде, капитан может быть только один;
учащиеся, группы и роли, староста может быть только один;
работники, подразделения и должности, руководитель подразделения может быть только один.

Типовое решение

Скрипт с типовым решением

Скрипт максимально упрощён, в нем отсутствуют «мягкое удаление», механизмы версионирования данных, проверка уникальности поля code оставлена регистрозависимой.

CREATE TABLE public.users (
	id integer NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
	code varchar(30) NOT NULL,
	CONSTRAINT users_pk PRIMARY KEY (id),
	CONSTRAINT users_unique_code UNIQUE (code)
);

CREATE TABLE public.groups (
	id integer NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1),
	code varchar(30) NOT NULL,
	CONSTRAINT groups_pk PRIMARY KEY (id),
	CONSTRAINT groups_unique_code UNIQUE (code)
);

CREATE TABLE public.roles (
	id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
	code varchar(30) NOT NULL,
	only_one bool NOT NULL DEFAULT false,
	CONSTRAINT roles_pk PRIMARY KEY (id),
	CONSTRAINT roles_unique_code UNIQUE (code)
);

CREATE TABLE public.links (
	id integer NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1),
	user_id integer NOT NULL,
	group_id integer NOT NULL,
	role_id integer NOT NULL,
	CONSTRAINT links_pk PRIMARY KEY (id),
	CONSTRAINT links_fk_users FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT links_fk_groups FOREIGN KEY (group_id) REFERENCES public.groups(id) ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT links_fk_roles FOREIGN KEY (role_id) REFERENCES public.roles(id) ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT links_unique_user_group_role UNIQUE (user_id, group_id, role_id)
);

Хотелось бы привлечь внимание к некоторым моментам:

для ограничений целесообразно явно указывать имена, а не полагаться на автоматические. Это удобнее (и надёжнее) при поддержке, когда возникает необходимость изменения или удаления ограничений.
для целочисленных полей primary key лучше использовать GENERATED ALWAYS AS IDENTITY, вместо GENERATED BY DEFAULT AS IDENTITY или series. Это позволяет на раннем этапе выявить ситуации, когда бэкенд пытается установить значение поля id. К сожалению, такие ошибки встречаются в проектах разного масштаба и, видимо, будут встречаться;
псевдо тип series удобен, он создаёт последовательность и связывает её с соответствующим полем. Однако имя последовательности присваивается автоматически, в будущем его придётся или «угадывать» (по правилам автоматического именования объектов) или явно получать из описания значения по умолчанию для поля.

В приведённом выше типовом решении выполнение условия «пользователи не должны входить в группу с одной и той же ролью несколько раз» контролируется ограничением links_unique_user_group_role.

Как возможно поступить с условием «для некоторых ролей в группе может быть не более одного пользователя»? Варианты:

контроль перенести на бэк. Подобная реализация рано или поздно может привести к нарушению условия. Возможные причины — ошибки реализации контроля в разных приложениях, параллельное редактирование таблицы в разных сеансах;
создать уникальный частичный индекс, который будет содержать id соответствующих ролей. Подобное решение требует изменения уникального индекса при изменении ролей. Изменение/пересоздание индекса не всегда возможно в произвольный момент времени, т.к. для этого необходимы соответствующие права доступа, построение индекса для большой таблицы занимает время, в период когда устаревший индекс удалён, а новый ещё не создан, данные или не контролируются или должны быть заблокированы для изменения;
использовать подход с дублированием данных поля only_one в таблицу links и создать уникальный частичный индекс, который не надо будет изменять при изменении ролей.

Подход с дублированием данных

Скрипт для корректировки типового решения

ALTER TABLE public.roles ADD CONSTRAINT roles_unique_id_only_one UNIQUE (id, only_one);

ALTER TABLE public.links ADD role_only_one bool NOT NULL;

ALTER TABLE public.links DROP CONSTRAINT links_fk_roles;
ALTER TABLE public.links ADD CONSTRAINT links_fk_roles_id_only_one FOREIGN KEY (role_id, role_only_one) REFERENCES roles(id, only_one) ON UPDATE CASCADE ON DELETE restrict

CREATE UNIQUE INDEX links_upidx_group_role ON public.links USING btree (group_id, role_id) WHERE (role_only_one);

Конечный вид таблиц roles и links после внесенных изменений

CREATE TABLE public.roles (
	id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
	code varchar(30) NOT NULL,
	only_one bool NOT NULL DEFAULT false,
	CONSTRAINT roles_pk PRIMARY KEY (id),
	CONSTRAINT roles_unique_code UNIQUE (code),
	CONSTRAINT roles_unique_id_only_one UNIQUE (id, only_one)
);

CREATE TABLE public.links (
	id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
	user_id integer NOT NULL,
	group_id integer NOT NULL,
	role_id integer NOT NULL,
	role_only_one bool NOT NULL,
	CONSTRAINT links_pk PRIMARY KEY (id),
	CONSTRAINT links_unique_user_group_role UNIQUE (user_id, group_id, role_id),
	CONSTRAINT links_fk_groups FOREIGN KEY (group_id) REFERENCES public."groups"(id) ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT links_fk_roles_id_only_one FOREIGN KEY (role_id,role_only_one) REFERENCES public.roles(id,only_one) ON DELETE RESTRICT ON UPDATE CASCADE,
	CONSTRAINT links_fk_users FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE RESTRICT ON UPDATE CASCADE
);
CREATE UNIQUE INDEX links_upidx_group_role ON public.links USING btree (group_id, role_id) WHERE role_only_one;

Заполнение данных и проверка

Создадим трёх игроков, две команды и две роли. Роль капитана может быть назначена не более чем одному из игроков.

INSERT INTO public.users (code) VALUES('user1'), ('user2'), ('user3');

INSERT INTO public.groups (code) VALUES('белая команда'), ('зелёная команда');

INSERT INTO public.roles (code, only_one) VALUES('игрок', false), ('капитан', true);

Поместим всех игроков в первую команду, одному из них присвоим роль капитана.

INSERT INTO public.links (user_id, group_id, role_id, role_only_one) VALUES(1, 1, 1, false), (1, 1, 2, true), (2, 1, 1, false), (3, 1, 1, false);

При попытке добавить ещё одного капитана получим ошибку вида ERROR: duplicate key value violates unique constraint «links_upidx_group_role».

Однако если мы в таблице roles у роли капитана снимем признак only_one (не забываем, что при этом каскадно обновятся соответствующие записи в links и перестроится индекс links_upidx_group_role), то сможем добавлять в команды дополнительных капитанов, ошибки не будет.

Если какой-то роли необходимо будет установить признак only_one, мы не сможем это сделать, пока не откорректируем данные в links.

Рассмотренное решение имеет следующие недостатки:

явное дублирование данных only_one;
необходимо правильно заполнять поле role_only_one в таблице links при создании связей;
добавляется уникальный индекс, который создаётся для ограничения roles_unique_id_only_one.

Для многих задач более высокое качество данных, которое может быть обеспечено ограничениями на уровне БД, уравновешивает перечисленные недостатки.

Теги:

Хабы:

Дублирование данных для создания ограничений (контролей) на уровне БД

Исходная типовая задача

Типовое решение

Подход с дублированием данных

Публикации

Истории

Работа

Ближайшие события