SQL DELETE: удаление строк из таблицы

Урок 8.3 · Время чтения: ~6 мин · Задачи для практики ↓

Оператор DELETE в SQL удаляет строки из таблицы. В этом уроке вы изучите синтаксис DELETE ... WHERE, научитесь удалять строки по условию на другой таблице, узнаете, почему внешние ключи иногда блокируют удаление, как проверить DELETE перед запуском и чем он отличается от TRUNCATE.

DELETE - последний из трёх операторов изменения данных. Его проще всего написать, и он самый опасный: одно пропущенное условие удаляет все строки таблицы. Правила этого урока помогают удалять ровно те строки, которые вы имеете в виду.

Учебные базы данных на SQLtest доступны только для чтения, поэтому там эти операторы вернут ошибку доступа. Примеры используют схему Sakila.

Синтаксис DELETE

DELETE FROM table_name
WHERE condition;

• DELETE FROM указывает таблицу.
• WHERE выбирает удаляемые строки. Без WHERE удаляются все строки.
• Удаляются только строки: таблица, её столбцы и индексы остаются.

Удаление строк по условию

DELETE FROM payment
WHERE payment_date < '2005-05-25';

Результат: удалены все платежи, сделанные до 25 мая 2005 года.

Удаление по первичному ключу удаляет ровно одну строку:

DELETE FROM payment
WHERE payment_id = 16049;

Удаление по условию на другой таблице

Подзапрос в WHERE выбирает строки по данным из других таблиц. Удалим платежи неактивных клиентов:

DELETE FROM payment
WHERE customer_id IN (
    SELECT customer_id
    FROM customer
    WHERE active = 0
);

Результат: удалены все платежи 15 неактивных клиентов.

Как и в случае с UPDATE, MySQL и MariaDB не позволяют подзапросу читать таблицу, из которой выполняется удаление. MySQL поддерживает также многотабличную форму: DELETE p FROM payment AS p INNER JOIN customer AS c ON c.customer_id = p.customer_id WHERE c.active = 0;.

Внешние ключи могут заблокировать DELETE

На клиентов ссылаются другие таблицы: rental и payment хранят customer_id. В Sakila эти внешние ключи заданы с ON DELETE RESTRICT, поэтому база данных отказывается удалять клиента, у которого ещё есть аренды или платежи:

DELETE FROM customer
WHERE customer_id = 1;

Результат: ошибка «Cannot delete or update a parent row: a foreign key constraint fails». Клиент остаётся на месте.

Это защищает данные: иначе аренды ссылались бы на клиента, которого больше не существует. Чтобы удалить такого клиента, сначала удалите или переназначьте зависимые строки, либо внешний ключ должен быть задан с ON DELETE CASCADE, который удаляет их автоматически. Параметры внешних ключей вы изучите вместе с CREATE TABLE в уроке 9.1.

Проверка перед удалением

1. Выполните SELECT с тем же WHERE и посмотрите на строки.
2. Выполните DELETE и сравните число затронутых строк.

-- 1. Какие строки будут удалены?
SELECT payment_id, customer_id, amount, payment_date
FROM payment
WHERE payment_date < '2005-05-25';

-- 2. Удалить ровно эти строки
DELETE FROM payment
WHERE payment_date < '2005-05-25';

В рабочей среде выполняйте важные удаления в транзакции и убедитесь, что есть резервная копия. Во многих системах бизнес-данные вообще не удаляют, а помечают строки как удалённые (active = 0, deleted_at) - это называется мягким удалением (soft delete).

DELETE без WHERE и TRUNCATE

DELETE FROM table_name; удаляет все строки, но оставляет таблицу. TRUNCATE TABLE table_name; делает то же самое гораздо быстрее, но работает иначе:

TRUNCATE и DROP TABLE вы изучите в уроке 9.3.

Ключевые выводы:

• DELETE FROM table WHERE condition удаляет подходящие строки; таблица остаётся.
• Без WHERE удаляются все строки.
• Подзапрос в WHERE позволяет удалять по условиям на других таблицах.
• Внешние ключи с RESTRICT блокируют удаление строк, на которые ещё ссылаются.
• Сначала проверяйте с помощью SELECT; для важных удалений используйте транзакции и резервные копии.
• Чтобы быстро очистить всю таблицу, используйте TRUNCATE.

Часто задаваемые вопросы

Что произойдёт, если выполнить DELETE без WHERE?

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

Почему в SQL не удаляется строка?

Обычно из-за внешнего ключа: на строку всё ещё ссылается другая таблица. Сначала удалите или измените зависимые строки либо задайте внешний ключ с ON DELETE CASCADE.

Чем отличаются DELETE и TRUNCATE?

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