Главная » Microsoft SQL Server, Базы данных » Удаление данных

0

Инструкция DELETE очень опасна. В своей простейшей форме она удаляет все строки таблицы. Так как эта инструкция работает с целыми строками, ей не требуется явного указания столбцов. Первое предложение FROM не является обязательным, равно как и второе предложение WHERE. Но несмотря на то, что предложение WHERE не обязательно, оно прежде всех остальных заботится о том, какие именно строки будут удаляться из таблицы. Вот сокращенный синтаксис инструкции DELETE:

DELETE [FROM] владелец.таблица [FROM источники_даиных]

[WHERE условия]

Обратите внимание на то, что все в этой инструкции является необязательным, за исключением разве что самого ключевого слова DELETE и имени таблицы. Следующая инструкция удалит все данные из таблицы Product — никаких вопросов она не задаст и никакого второго шанса не предоставит:

DELETE

FROM OBXKites.dbo.Product

SQL Server не имеет встроенной команды отмены операции. Как только транзакция завершена, все изменения становятся свершившимся фактом. Именно поэтому в инструкции DELETE такое большое значение имеет предложение WHERE.

До сих пор самым распространенным местом применения инструкции DELETE было удаление одной строки. Механизмом выбора этой строки обычно служил первичный ключ:

USE OBXKites

DELETE FROM dbo.Product

WHERE ProductID = ‘DB8D8D60-76F4-46C3-90E6-A8648F63C0F0′

Ссылка при удалении на множество таблиц

Инструкция UPDATE использует предложение FROM для объединения обновляемой таблицы с другими для более гибкого отбора строк. Инструкция DELETE использует ту же методику. В то же время немного странный вид ей придает первое необязательное предложение

FROM. Чтобы сделать текст программы более понятным и последовательным, я бы рекомендовал опускать это предложение.

Например, следующая инструкция DELETE игнорирует первое предложение FROM и использует только второе для объединения таблицы Product с таблицей ProductCategory, чтобы предложение WHERE имело возможность отфильтровать нужные строки по полю названия категории (ProductCategoryName). Предлагаемый в качестве примера запрос удаляет все строки, относящиеся к видео, из таблицы Product:

DELETE Product

FROM dbo.Product JOIN ProductCategory

ON Procduct.ProductCategorylD

= ProductCategory.ProductCategorylD WHERE ProductcategoryName = ‘Video’

Как и в предложении FROM инструкции UPDATE, второе предложение FROM инструкции DELETE не относится к стандарту ANSI SQL. Если для вашего проекта имеют значение вопросы переносимости, то для ссылок на дополнительные таблицы используйте подзапросы.

Каскадные удаления

Ссылочная целостность подразумевает тот факт, что ни одна вторичная строка не может ссылаться на первичную, которой на самом деле не существует. Это значит, что любая попытка удаления первичной строки завершится ошибкой, если какое-либо значение внешнего ключа ссылается на нее.

Дополнительная Подробно вопрос ссылочной целостности и ее использования разбирается в информация главах 2 и 17.

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

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

К счастью, в версии SQL Server 2000 каскадные удаления предложены как функция внешнего ключа. Каскадные удаления можно включить в Management Studio (рис. 16.2), а также с помошью кода T-SQL.

В примере сценария, который создает учебную базу данных Cape Hatteras Adventures версии 2 (CHA2_Create. sql). содержится отличный пример настройки каскадного удаления для поддержания ссылочной целостности. В данном случае, если удаляется событие или маршрут, удаляются также и соответствующие строки связывающей таблицы, реализующей отношение “многие ко многим”. Параметр ON DELETE CASCADE внешнего ключа определяет такой режим удаления:

CREATE TABLE dbo.Event_mm_Guide (

EventGuidelD

INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED, EventID

INT NOT NULL

FOREIGN KEY REFERENCES dbo.Event ON DELETE CASCADE, GuidelD

INT NOT NULL

FOREIGN KEY REFERENCES dbo.Guide ON DELETE CASCADE, LastName

VARCHAR(50) NOT NULL,

)

ON [Primary]

Puc. 16.2. Настройка в Management Studio внешних ключей для каскадного удаления

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

В качестве примера такой ситуации можно привести операцию удаления тура из базы данных Cape Hatteras Adventures. В этом случае все события, связанные с туром, теряют смысл, равно как и относящиеся к нему строки связующих таблиц (между событием и заказчиком и между событием и гидом).

Любой тур должен иметь базовый лагерь, так что для таблицы Tour требуется поддержание ссылочной целостности. В то же время, если удаляется какой-либо базовый лагерь, тур, начинающийся в нем, должен остаться доступным (он может быть назначен другому базовому лагерю). Таким образом, каскадное удаление от базового лагеря к туру не обосновано.

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

Альтернативы физическому удалению данных

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

?               В строке может существовать логический битовый флажок, указывающий на то, что строка удалена. Такой подход упрощает удаление и восстановление одной строки — для этого нужно всего лишь изменить одно битовое значение. Но поскольку реляционная база данных содержит множество связанных между собою таблиц, работы оказывается больше, чем кажется на первый взгляд. Все запросы должны проверять наличие флага логического удаления и фильтровать соответствующим образом строки. К тому же, так как строки остаются физически существовать в SQL Server, а система поддержания ссылочной целостности сервера вообще не знает о существовании флага удаления, может потребоваться поддержание целостности на программном уровне. Ее сложность зависит от того, насколько далеко вы хотите распространить логическое удаление. Этот метод ускоряет операции логического удаления, однако сильно замедляет выполнение отбора записей. Каскадное логическое удаление может стать очень сложным, а восстановление записей — вообще превратиться в кошмар.

?               Еще одна альтернатива сводится к физическому архивированию удаленных строк в другую таблицу или базу данных. Этот метод хорошо реализуется с помощью хранимых процедур, которые вставляют удаленные строки в архив, а затем удаляют их из основной базы данных.

Этот метод имеет ряд достоинств. Данные физически удаляются из основной базы данных, так что отпадает необходимость искусственно изменять инструкции SELECT. Используя распределенные представления или федеративную схему базы данных, можно упростить включение в запросы отбора информации из нескольких баз данных. Физическое удаление данных позволяет без помех работать системе поддержания ссылочной целостности. К этому следует добавить, что основная база данных не засоряется ненужной информацией. Извлечение архивных данных остается при этом простым, хотя на администратора поддержка архива возлагает дополнительную нагрузку.

Дополнительная В главе 53 вы больше узнаете о распределенных представлениях, а вся часть V информация будет посвящена стратегиям поддержки хранилищ данных и доставки инфор- ^ " мации из них конечным пользователям.

?               Наиболее совершенной альтернативой удаления строк является поддержание полного аудита операций модификации данных. Это позволит не только отследить полную цепочку обновлений, но и восстановить удаленные строки. В то же время ведение аудита влечет появление накладных расходов, связанных со сложностью, производительностью и объемом хранимых данных.

Дополнительная В главе 24 описано, как создать триггеры, выполняющие каскадные удаления, информация поддерживающие ссылочную целостность, создающие журналы аудита, архи-

„ . — вирующие данные и выполняющие логическое удаление строк.

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии