пятница, 28 января 2011 г.

Основы SQL – DML – DELETE

Продолжаю серию статей “Основы SQL”. В предыдущих сериях:
В этой статье я завершу рассказ про операторы 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-инструкциями (в т.ч. для предыдущих статей).

7 комментариев:

  1. Иногда, когда обработка происходит по одной записи, можно запретить множественные удаления и изменения.

    ОтветитьУдалить
  2. Каскадное удаление в 3х реализациях приведенных Вами в реальных бизнес логиках не катит абсолютно!
    Что делать, если надо не просто удалить код, на который ссылается куча дочерних записей, а, например, прописать в дочерние записи какой-то код (но не дэфалтный) и удалить родителя. Как это реализовать каскадным удалением?

    ОтветитьУдалить
  3. To @Тим:
    Да, иногда такие ограничения существенно упрощают код.

    To @Анонимный:
    Цитирую статью: *Как правило*, этих вариантов хватает на все случаи жизни.
    Лично я участвовал приблизительно в трех десятках проектов, разного профиля и сложности, не хватало стандартных вариантов только в паре случаев.
    Другой вопрос, что иногда каскадное удаление подходит, но, при этом, все равно его не используешь (например, когда ORM это поддерживает и важна переносимость на другие СУБД).

    ОтветитьУдалить
  4. да, циклы - это действительно настоящая беда! жду статьи об использовании триггеров для ее решения...

    ОтветитьУдалить
  5. To @Анонимный:

    Тема триггеров для удаления не "тянет" на статью - там же просто делается INSTEAD OF триггер и в нем предварительно удаляются родительские данные. Или речь о чем-то еще?

    ОтветитьУдалить
  6. Тогда для начала ждем тему про триггеры ;-)

    ОтветитьУдалить
  7. Про триггеры попробую написать в следующей серии статей, ближе к маю, сначала закончу эту.

    ОтветитьУдалить