Удаление и изменение данных с учётом внешних ключей

Итак, мы уже знаем, что такое внешние ключи (FOREIGN KEY), как они работают, и даже потренировались в создании таблиц с их использованием. Но как быть, если пришло время удалить данные или изменить связанные записи? Вот мы сейчас и разберемся, как удаление и изменение данных работают в связке с внешними ключами, и какие тут есть хитрости.

Представьте, что база данных — это сложный карточный домик. Если вы вынимаете одну карту, то рискуете разрушить всю конструкцию. Вот тут-то и вступают в игру внешние ключи, которые предотвращают "разрушение" нашей базы при удалении связанных данных. Давайте разберемся, как это работает.

Что произойдет, если попытаться удалить данные?

Когда в таблице есть внешний ключ, СУБД проверяет, связана ли запись с другими таблицами. Если она связана, то попытка удалить запись может вызвать ошибку целостности данных. Чтобы таких сюрпризов не было, можно заранее задавать действия для внешнего ключа. Эти действия настраиваются с помощью опций ON DELETE.

Настройка поведения с ON DELETE

Вы можете задать одно из следующих правил для внешнего ключа в момент его создания:

1. ON DELETE CASCADE: Удаление записи в родительской таблице автоматически приводит к удалению всех связанных записей в дочерней таблице.
2. ON DELETE SET NULL: Вместо удаления связанных записей их внешние ключи устанавливаются в NULL.
3. ON DELETE SET DEFAULT: Значение внешнего ключа устанавливается в значение по умолчанию.
4. ON DELETE RESTRICT (поведение по умолчанию): Удаление записи невозможно, если есть связанные записи, и будет выброшена ошибка.
5. ON DELETE NO ACTION: Почти как RESTRICT, но проверка целостности отложена до завершения транзакции.

Пример: Каскадное удаление ON DELETE CASCADE

-- Таблица клиентов
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');

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

-- Проверяем, что произошло с таблицей orders
SELECT * FROM orders; -- Никаких записей, они были удалены каскадно!

Когда мы удаляем запись из таблицы customers, PostgreSQL автоматически удаляет все заказы этого клиента из таблицы orders.

Пример: Установка ссылок в NULL ON DELETE SET NULL

-- Таблица заказов с новым правилом поведения
CREATE TABLE orders_with_null (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE SET NULL,
    order_date DATE NOT NULL
);

-- Вставка данных
INSERT INTO orders_with_null (customer_id, order_date) VALUES (1, '2023-10-01');

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

-- Проверяем таблицу orders_with_null
SELECT * FROM orders_with_null;

Вывод:

С помощью ON DELETE SET NULL мы можем сохранить заказы, но "отвязать" их от теперь уже несуществующего клиента.

Изменение данных с учётом внешних ключей

Помимо удаления, изменения данных в родительских таблицах тоже могут влиять на связанные записи. Например, что произойдет, если клиент меняет свой customer_id? А вот тут вступает в игру опция ON UPDATE.

Настройка поведения с ON UPDATE

Обработать изменения в родительской таблице можно с помощью следующих стратегий:

1. ON UPDATE CASCADE: изменение значения внешнего ключа в родительской таблице автоматически обновляет его во всех связанных записях.
2. ON UPDATE SET NULL: значение внешнего ключа в дочерних таблицах устанавливается в NULL.
3. ON UPDATE SET DEFAULT: устанавливается значение по умолчанию.
4. ON UPDATE RESTRICT: изменение значения внешнего ключа запрещено, если есть связанные записи.
5. ON UPDATE NO ACTION: проверка откладывается до конца транзакции.

Пример: Каскадное обновление ON UPDATE CASCADE

CREATE TABLE customers_with_cascade (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders_with_cascade (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers_with_cascade(customer_id) ON UPDATE CASCADE,
    order_date DATE NOT NULL
);

-- Вставка данных
INSERT INTO customers_with_cascade (name) VALUES ('Иван Иванов');
INSERT INTO orders_with_cascade (customer_id, order_date) VALUES (1, '2023-10-01');

-- Изменение customer_id
UPDATE customers_with_cascade SET customer_id = 100 WHERE customer_id = 1;

-- Проверяем таблицу orders_with_cascade
SELECT * FROM orders_with_cascade;

Вывод:

При изменении customer_id PostgreSQL автоматически обновляет его в таблице orders_with_cascade.

Практическое закрепление: Таблица enrollments

Давайте вспомним наш пример со студентами students и курсами courses. Мы будем работать с таблицей записей на курсыenrollments и настраивать управление удалением данных.

-- Таблица студентов
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- Таблица курсов
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

-- Промежуточная таблица записей
CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id) ON DELETE CASCADE,
    course_id INT REFERENCES courses(course_id) ON DELETE CASCADE,
    PRIMARY KEY (student_id, course_id)
);

-- Вставка данных
INSERT INTO students (name) VALUES ('Алексей Петров');
INSERT INTO courses (title) VALUES ('Математика');
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);

-- Удаляем студента
DELETE FROM students WHERE student_id = 1;

-- Проверяем таблицу enrollments
SELECT * FROM enrollments; -- Пусто! Запись была автоматически удалена.

Типичные ошибки и их предотвращение

Одной из частых ошибок является попытка удалить запись из родительской таблицы, не настроив корректное поведение внешних ключей. Например, если вы не указали ничего для ON DELETE, по умолчанию будет применено поведение RESTRICT, что приведет к ошибке.

Также важно помнить, что слишком частое использование каскадных операций (CASCADE) может привести к неожиданным последствиям. Например, вы можете случайно удалить больше данных, чем предполагали.

Чтобы избежать подобных проблем, следуйте этим рекомендациям:

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

2

Задача

SQL SELF, 20 уровень, 3 лекция

Недоступна

Удаление с ограничением

Удаление с ограничением

Открыть

2

Задача

SQL SELF, 20 уровень, 3 лекция

Недоступна

Каскадное удаление

Каскадное удаление

Открыть