Основы SQL – DML – DELETE

Продолжаю серию статей “Основы SQL”. В предыдущих сериях:

• Первое знакомство
• DDL и рефакторинг БД
• DML – INSERT и другие
• DML – UPDATE

В этой статье я завершу рассказ про операторы DML, изменяющие данные - речь пойдет про DELETE – оператор, с помощью которого можно удалить одну или много строк в таблице, или много строк во многих таблицах… главное – не слишком увлекаться :)

Удаление строк

Удаление строк более простая операция чем все остальные в DML, по большому счету, все сказанное относительно UPDATE применимо и к DELETE, с той лишь разницей, что данные удаляются, а не модифицируются, и синтаксис немного другой:

DELETE Phones WHERE PersonID = 1 AND Phone = '100-1'

В этом примере происходит удаление одной строки (если такая есть), потому что, как вы помните, “PersonID” и “Phone” являются составным первичным ключом.


При всей простоте, для DELETE чаще встречается проблема с внешними ключами, чем с оператором INSERT (отсутствие записей в родительских таблицах все-таки редкость) и UPDATE (первичные/уникальные ключи редко обновляются).
Проблема – немного неправильная формулировка, скорее просто для обработки подобных ситуаций иногда необходимо совершить дополнительные телодвижения. Хорошая новость в том, что во многих СУБД (и MS SQL Server не исключение) поддерживаются каскадные операции над внешними ключами. В частности, для удаления мы можем задать при создании внешнего ключа следующие операции:

• оставить поведение по умолчанию – ошибку при удалении родительской записи, когда существует хотя бы одна дочерняя;
• установить значение этого столбца в соответствующих строках дочерней таблицы в NULL (ON DELETE SET NULL) или значение по умолчанию (ON DELETE SET DEFAULT);
• удалить все дочерние записи (ON DELETE CASCADE).

Как правило, этих вариантов хватает на все случаи жизни (применяется тот или иной, в зависимости от бизнес-логики). Для UPDATE, кстати, есть те же варианты, но лично мне их почти не приходилось использовать.

А плохая новость в том, что когда в полученном графе из каскадных внешних ключей встречаются циклы, MS SQL Server честно посылает нас… реализовывать дополнительную обработку в коде приложения или триггерах.

Важно: не забывайте о проверке корректности/наличия фильтра, перед запуском обновления или удаления!

Об этом я уже говорил в предыдущей статье, но это действительно важно. Хотя, как показывает практика, после первой серьезной ошибки подобного рода, они обычно начинают встречаться реже чем раз в год. Если только не практиковать анти-практику XP Non-energized work :)

Удаление с фильтром по другой таблице

При удалении (равно как и при обновлении) данных можно использовать данные из других таблиц. Допустим, мы хотим удалить все телефоны Иванова из нашей базы (причем неважно, сколько таких Ивановых у нас записано).

Сделать это можно с помощью удаления из таблицы Phones, но дополнительно с указанием таблицы Persons во фразе FROM (заодно можно понаблюдать за работой транзакций, потому что мне не хотелось восстанавливать тестовые данные):

BEGIN TRAN
    DELETE Phones FROM Persons
        WHERE Persons.PersonID = Phones.PersonID 
        AND LastName = 'Иванов'
ROLLBACK

Если интересно, можете попробовать выбрать записи из таблицы Phones до и после Rollback (если вы выполняли скрипты последовательно, должны быть разные по количеству строк результаты).

Есть более объемный, но иногда более наглядный вариант того же самого действия:

BEGIN TRAN
    DELETE Phones FROM Phones 
        JOIN Persons ON Persons.PersonID = Phones.PersonID
        WHERE AND LastName = 'Иванов'
ROLLBACK

Более подробно о фразе JOIN (и почему она мне в большинстве случаев нравится больше, чем перечисление таблиц через запятые) я расскажу позже. А пока на всякий случай добавлю, что никто не мешает (кроме здравого смысла, в некоторых случаях) добавлять новые таблицы с помощью новых JOIN и строить более сложные фильтры.

За кадром

Оператор DELETE весьма простой. Тем не менее, некоторые детали его синтаксиса остались за кадром. За подробностями можно обратиться к статье MSDN про DELETE.

В MS SQL Server 2008 появился новый оператор DML – MERGE. Как следует из названия, он предназначен для слияния данных (и может как бы выполнять INSERT, UPDATE и DELETE в рамках одного оператора).

Анонс

В следующих сериях:

• DML - SELECT
• Рефакторинг с учетом данных
• Подробнее о запросах
• Управляем доступом и транзакциями

Если у кого-нибудь есть пожелания или даже новые темы к статьям из этой серии – пишите в комментариях. Постараюсь принять к сведению.

UPD: можно скачать скрипт с DML-инструкциями (в т.ч. для предыдущих статей).