Использование ограничений на стороне базы данных, таких как внешние ключи, проверки значений, требования уникальности, иногда вызывают споры среди разработчиков. Аргумменты «за» и «против» обеих сторон хорошо известны.
Рассмотрим пример, когда ограничения не просто применяются, а реализуют дополнительную логику с помощью дублирования некоторых данных.
Это могут быть:
Хотелось бы привлечь внимание к некоторым моментам:
В приведённом выше типовом решении выполнение условия «пользователи не должны входить в группу с одной и той же ролью несколько раз» контролируется ограничением links_unique_user_group_role.
Как возможно поступить с условием «для некоторых ролей в группе может быть не более одного пользователя»? Варианты:
Рассмотренное решение имеет следующие недостатки:
Для многих задач более высокое качество данных, которое может быть обеспечено ограничениями на уровне БД, уравновешивает перечисленные недостатки.
Рассмотрим пример, когда ограничения не просто применяются, а реализуют дополнительную логику с помощью дублирования некоторых данных.
Исходная типовая задача
- есть пользователи, группы и роли;
- пользователи могут входить в группы с указанием роли;
- пользователи не должны входить в группу с одной и той же ролью несколько раз;
- для некоторых ролей в группе может быть не более одного пользователя.
Это могут быть:
- игроки, команды и роли в команде, капитан может быть только один;
- учащиеся, группы и роли, староста может быть только один;
- работники, подразделения и должности, руководитель подразделения может быть только один.
Типовое решение
Скрипт с типовым решением
Скрипт максимально упрощён, в нем отсутствуют «мягкое удаление», механизмы версионирования данных, проверка уникальности поля 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 int4 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 и создать уникальный частичный индекс, который не надо будет изменять при изменении ролей.
Подход с дублированием данных
Скрипт для корректировки типового решения
Конечный вид таблиц roles и 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;
Заполнение данных и проверка
Создадим трёх игроков, две команды и две роли. Роль капитана может быть назначена не более чем одному из игроков.
Поместим всех игроков в первую команду, одному из них присвоим роль капитана.
При попытке добавить ещё одного капитана получим ошибку вида ERROR: duplicate key value violates unique constraint «links_upidx_group_role».
Однако если мы в таблице roles у роли капитана снимем признак only_one (не забываем, что при этом каскадно обновятся соответствующие записи в links и перестроится индекс links_upidx_group_role), то сможем добавлять в команды дополнительных капитанов, ошибки не будет.
Если какой-то роли необходимо будет установить признак only_one, мы не сможем это сделать, пока не откорректируем данные в links.
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.
Для многих задач более высокое качество данных, которое может быть обеспечено ограничениями на уровне БД, уравновешивает перечисленные недостатки.
Akina
Для начала вспомним известную истину: Всё, что не указано явно, может быть как угодно.
С учётом этого постулата описанная Исходная типовая задача на самом деле не описана даже наполовину.
После внимательного изучения приведённого типового решения и сравнения его с теми ограничениями, что имеются в задаче, можно дальше не читать - решение в общем случае заведомо ошибочное, и применимо лишь к частным случаям, когда в задаче на самом деле присутствует большая куча дополнительных условий и ограничений, в текущей формулировке задачи отсутствующих.
В общем случае - должна существовать связующая таблица, связывающая группу и роль, флаг единственности должен быть атрибутом именно этой сущности "роль в группе", а уже юзеры должны линковаться на эту сущность - через ещё одну связующую таблицу.
nvv Автор
Вы правы, если признак единственности роли в группе не является свойством роли и устанавливается на основе других бизнес правил, эту связь необходимо вынести в отдельную таблицу. Для максимально упрощенного примера в статье это будет излишним.
Akina
В том и проблема, что это не решение задачки в лабораторной работе, а статья (осмелюсь предположить, что она задумана как обучающая статья). А потому частичное формулирование задачи, а потом решение только одной из её вариаций (причём даже в решении все дополнительные ограничения не описаны - я уж не говорю о пояснениях, как изменится решение, если этот набор дополнительных ограничений изменится) - явно некорректный подход.
nvv Автор
Модельная задача описана полностью и рассмотрены варианты решения.
Какие дополнительные условия вы хотели бы предложить включить?
Akina
Первый, и самый главный вопрос - группы абсолютно однородны? Например, если в одной группе некая роль должна присутствовать в единственном экземпляре, то означает ли это, что эта же роль в любой другой группе также должна присутствовать в единственном экземпляре?
Второй вопрос - может ли один и тот же пользователь входить в группу несколько раз с разными ролями?
Даже ответов на эти два вопроса в вариантах ДА-НЕТ вполне достаточно, чтобы породить четыре совершенно разные схемы с совершенно разной реализацией на уровне SQL - причём каждая из этих схем имеет вполне реальные практические воплощения.
nvv Автор
В модельной задаче, которая специально упрощена, чтобы не перегружать ее и сконцентрироваться на особенностях решения с дублироваеием данных, эти вопросы не существенны.
По вопросу N1 - явно указана зависимость от роли, зависимости от группы нет в описании и для демонстрации подхода нет необходимости её добавлять. По вопросу N2 - такое ограничение не указано и уточнение наоичия/отсутствия ограничения не поможет.
В реальных задачах перечень вопросов, которые должны быть проработаны до реализации, может быть очень широким и предложенные вами вопросы конечно должны входить в этот список.