PostgreSQL | Запрет обновления ячеек столбца таблицы

Как в PostgreSQL запретить любое обновление всех существующих ячеек определённого столбца таблицы? Возможно только удаление записи с ячейкой.

В PostgreSQL по умолчанию при создании таблицы предусматривается возможность изменения (UPDATE) любых данных, на которые не наложены какие-либо ограничения. Есть базовые ограничения первичного ключа или внешнего ключа. Также из популярных может быть ограничение на пустое значение или уникальности значения ячейки по столбцу для всей таблицы. Это всё очень полезно и освобождает разработчика от написания лишних проверок.

И вот однажды возникает потребность в создании такого ограничения из-за которого становится невозможным обновление записи таблицы, если в этом обновлении затрагивается определённая ячейка записи — определённый столбец.

Например. У нас есть таблица с метками. При создании новой метки мы хотим заблокировать за ней уникальный идентификатор (первичный ключ) и уникальное имя (имя метки). Даже если у нас есть ограничение уникальности для всего столбца меток, то это не запрещает нам переименовать метку на какую-нибудь несуществующую в данной таблице.

 

Ограничение на обновление ячейки в PostgreSQL

Если у нас есть метки «спорт«, «еда«, «культура«, то нам ничего не мешает заменить метку «спорт» на «спортивные мероприятия«. Уникальность значений столбца сохранится, но смысл метки поменяется. А если мы изменим метку «спорт» на «космос«, то это будет совсем печально. И тут важно не забывать, что наш первичный ключ может быть внешним ключом в каких-то других таблицах. Такое обновление (UPDATE) может привести к неразберихе в приложении на клиентской стороне (сайте). Нужен запрет на изменение данных в определённой ячейки — изменение возможно только через удаление (DELETE) и создание новой записи (INSERT).

 

Таблица, в которой будем запрещать обновление ячеек столбца «label»

-- DROP TABLE IF EXISTS test.t_label;
CREATE TABLE test.t_label(
  id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
  label text NOT NULL,
  description text NOT NULL DEFAULT '',
  datecreate timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  datecheck timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT test_t_label_pkey PRIMARY KEY (id),
  CONSTRAINT test_t_label_unique_label UNIQUE (label)
);

 

«Триггерная функция», которая выбрасывает заранее определённую ошибку и отклоняет выполнение «триггера» на обновление конкретного столбца

-- DROP FUNCTION IF EXISTS test.up_label();
CREATE OR REPLACE FUNCTION test.up_label()
RETURNS trigger
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
  RAISE EXCEPTION 'Обновления для столбца "label" ЗАБЛОКИРОВАНЫ триггером "up_label" для таблицы "test.t_label"!';
END;
$BODY$;

 

Триггер, который реагирует на обновление ячейки определённого столбца таблицы

-- DROP TRIGGER IF EXISTS up_label ON test.t_label;
CREATE OR REPLACE TRIGGER up_label
BEFORE UPDATE ON test.t_label
FOR EACH ROW
WHEN (OLD.label IS DISTINCT FROM NEW.label)
EXECUTE FUNCTION test.up_label();

 

Вставка данных. Тестирование запрета обновления ячейки в PostgreSQL

Сначала вставим одну запись в таблицу и убедимся что наш триггер никак не затрагивает событие INSERT.

-- Вставка данных
INSERT INTO test.t_label (label, description) VALUES ('Еда', 'Сайты ресторанов, кафе, баров');
INSERT INTO test.t_label (label, description) VALUES ('Спорт', 'Сайты спортивных клубов, стадионов');

Результат вставки:

 

Теперь пробуем заменить «метку» для первой записи с идентификатором 1.

-- Обновление ЗАПРЕЩЁННОГО столбца
UPDATE test.t_label SET label = 'ЕдаМоя' WHERE id = 1;

Результат вызова команды обновления запрещённого столбца:

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

 

Теперь пробуем заменить «описание» для первой записи с идентификатором 1.

-- Обновление РАЗРЕШЁННОГО столбца
UPDATE test.t_label SET description = 'Новое описание' WHERE id = 1;

Результат вызова команды обновления разрешённого столбца:

Изменение значения ячейки с столбце «description» никак не повлияло на работу триггера. Обновление прошло успешно.

 

Если мы попытаемся обновить значения в двух столбцах, то мы получим ошибку, так как будет затронут запрещённый для обновления столбец.

-- Обновление сразу двух столбцов
UPDATE test.t_label SET label = 'ЕдаМоя', description = 'Новое описание' WHERE id = 1;

Результат:

 

И напоследок самый странный случай, который технически выполняется, но не мешает работе триггера. Это случай, когда в запрещённой ячейке значение пытается обновиться на точно такое же:

-- Обновление на то же самое значение
UPDATE test.t_label SET label = 'Еда' WHERE id = 1;

Результат:

 

Итог

Сначала создаём триггер, в котором пишем нужное нам условие WHEN. Затем под этот конкретный триггер пишем функцию, которая просто вызывает исключение EXCEPTION если обновляемое значение отличается от текущего.

В результате, исключение EXCEPTION полностью отменяет попытку обновления ячейки в столбце «label», так как не выполняется условие равенства «старого» и «нового» значений.

При этом мы без проблем можем менять значения в столбцах «description«, «datecreate» и «datecheck«.

 

Информационные ссылки

Официальный сайт WEB-оболочки pgAdmin — https://www.pgadmin.org

Официальный сайт СУБД PostgreSQL — https://www.postgresql.org

Команды SQL — https://postgrespro.ru/docs/postgresql/15/sql-commands

Команда CREATE TRIGGER — https://postgrespro.ru/docs/postgresql/15/sql-createtrigger

Раздел Триггерные функции — https://postgrespro.ru/docs/postgresql/15/plpgsql-trigger

Раздел Сообщения и ошибки — https://postgrespro.ru/docs/postgresql/15/plpgsql-errors-and-messages