Использование ON DELETE CASCADE и ON UPDATE RESTRICT

Представьте, что вы создали базу данных, где есть таблица клиентов (customers) и связанные с ними заказы (orders). Однако в какой-то момент возникает задача: что делать, если клиент удаляется из таблицы customers? Должны ли заказы этого клиента тоже удаляться, или они останутся "сиротами", ссылаясь на несуществующего клиента? А что, если вы решите изменить ID клиента? Вот где на арену выходят каскадные операции (CASCADE) и ограничения (RESTRICT) для управления поведением базы данных.

ON DELETE CASCADE — это механизм, который автоматически удаляет связанные записи при удалении записи из родительской таблицы. Другими словами, если вы удалили клиента, то все связанные с ним заказы тоже удалятся.

Это работает следующим образом. Когда вы добавляете ON DELETE CASCADE в определение внешнего ключа, база данных "понимает", что связанная запись должна быть удалена автоматически.

Пример

Допустим, у нас есть две таблицы: customers и orders. Клиенты customers могут сделать несколько заказов orders, что соответствует связи ONE-TO-MANY. Мы хотим, чтобы при удалении клиента все его заказы также удалялись.

-- Создаем таблицу клиентов
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Создаем таблицу заказов с внешним ключом, ссылающимся на таблицу клиентов
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    order_date DATE NOT NULL
);

Вставим данные в таблицы

-- Вставляем данные в таблицу клиентов
INSERT INTO customers (name) VALUES ('Иван'), ('Анна');

-- Вставляем данные в таблицу заказов
INSERT INTO orders (customer_id, order_date) VALUES
(1, '2023-10-01'),
(1, '2023-10-02'),
(2, '2023-10-03');

Таблица orders:

Удаляем клиента и проверяем, что произойдет

-- Удаляем клиента с ID 1
DELETE FROM customers WHERE customer_id = 1;

-- Проверяем, что осталось в таблице заказов
SELECT * FROM orders;

Как видим, заказы, связанные с удаленным клиентом, также были удалены.

Ограничение изменений: ON UPDATE RESTRICT

ON UPDATE RESTRICT позволяет предотвратить изменение значения в родительской таблице, если запись в дочерней таблице ссылается на это значение. Это своеобразный "защитный барьер", который предотвращает изменения, способные нарушить целостность данных.

Как это работает? Когда вы добавляете ON UPDATE RESTRICT, база данных не позволит обновить ключ в родительской таблице, если на него ссылаются записи в дочерней таблице.

Пример

Мы берем всё те же таблицы customers и orders, но добавим к внешнему ключу ограничение изменения.

-- Пересоздаем таблицу заказов с ограничением на обновление
DROP TABLE orders;

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON UPDATE RESTRICT,
    order_date DATE NOT NULL
);

Попробуем обновить ID клиента

-- Попытка изменить ID клиента с 2 на 5
UPDATE customers 
SET customer_id = 5 
WHERE customer_id = 2;

Результат:

ERROR:  update or delete on table "customers" violates foreign key constraint
DETAIL:  Key (customer_id)=(2) is still referenced from table "orders".

Как видите, база данных выбросила ошибку, так как изменение ключа нарушило бы связь между таблицами.

Подробнее про UPDATE и нюансы его работы я расскажу в следующем уровне :P

Совмещение ON DELETE CASCADE и ON UPDATE RESTRICT

Разумеется, каскадные операции (CASCADE) и ограничения (RESTRICT) можно сочетать. Например, вы можете настроить автоматическое удаление связанных данных при удалении родительской записи (ON DELETE CASCADE), но при этом запретить изменение её ID (ON UPDATE RESTRICT), чтобы избежать нежелательных последствий.

Пример

Создадим ещё раз таблицу заказов, используя оба механизма:

DROP TABLE orders;

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE RESTRICT,
    order_date DATE NOT NULL
);

Теперь:

• Если вы удалите клиента, все его заказы будут удалены.
• Если вы попытаетесь изменить ID клиента, получите ошибку.

Почему это важно в реальных проектах?

Использование CASCADE и RESTRICT очень важно для крупных систем с большим количеством связанных таблиц. Например:

В интернет-магазине клиент может иметь заказы. Если клиент решит удалить свой профиль, вы не хотите оставлять в базе данных заказы, которые уже ни на что не ссылаются. Здесь на помощь придет ON DELETE CASCADE.

В то же время, вы можете захотеть предотвратить случайные изменения уникальных ключей, чтобы не разрушить связи между таблицами. Для этого пригодится ON UPDATE RESTRICT.

2

Задача

SQL SELF, 19 уровень, 2 лекция

Недоступна

Создание таблиц с `ON DELETE CASCADE`

Создание таблиц с `ON DELETE CASCADE`

Открыть

2

Задача

SQL SELF, 19 уровень, 2 лекция

Недоступна

Добавление ограничения `ON UPDATE RESTRICT`

Добавление ограничения `ON UPDATE RESTRICT`

Открыть