Видалення даних

Інструкція DELETE дуже небезпечна У своїй найпростішої формі вона видаляє всі рядки таблиці Так як ця інструкція працює з цілими рядками, їй не потрібно явної вказівки стовпців Перша пропозиція FROM не є обовязковим, так само як і друге речення WHERE Але незважаючи на те, що пропозиція WHERE не обовязково, воно перш всіх інших піклується про те, які саме рядки будуть видалятися з таблиці Ось скорочений синтаксис інструкції DELETE:

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

[WHERE умови]

Зверніть увагу на те, що все в цьому інструкції є необовязковим, за винятком хіба що самого ключового слова DELETE та імені таблиці Наступна інструкція видалить всі дані з таблиці Product – Ніяких питань вона не здасть і ніякого другого шансу не надасть:

DELETE

FROM OBXKitesdboProduct

SQL Server не має вбудованої команди скасування операції Як тільки транзакція завершена, всі зміни стають доконаним фактом Саме тому в інструкції DELETE таке велике значення має пропозицію WHERE

Досі найпоширенішим місцем застосування інструкції DELETE було видалення одного рядка Механізмом вибору цього рядка зазвичай служив первинний ключ:

USE OBXKites

DELETE FROM dboProduct

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

Посилання при видаленні на безліч таблиць

Інструкція UPDATE використовує пропозицію FROM для обєднання оновлюваної таблиці з іншими для більш гнучкого відбору рядків Інструкція DELETE використовує ту ж методику У той же час трохи дивний вигляд їй додає перший необовязкове пропозицію

FROM Щоб зробити текст програми більш зрозумілим і послідовним, я б рекомендував опускати цю пропозицію

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

DELETE Product

FROM dboProduct JOIN ProductCategory

ON ProcductProductCategorylD

= ProductCategoryProductCategorylD WHERE ProductcategoryName = Video

Як і в реченні FROM інструкції UPDATE, друге речення FROM інструкції DELETE не відноситься до стандарту ANSI SQL Якщо для вашого проекту мають значення питання переносимості, то для посилань на додаткові таблиці використовуйте підзапити

Каскадні видалення

Посилальна цілісність увазі той факт, що жодна вторинна рядок не може посилатися на первинну, якої насправді не існує Це означає, що будь-яка спроба видалення первинної рядки завершиться помилкою, якщо яке-небудь значення зовнішнього ключа посилається на неї

Додаткова Детально питання посилальної цілісності та її використання розбирається в інформація розділах 2 і 17

Механізм підтримки посилальної цілісності блокуватиме операції, що її порушують Єдиним способом обходу цього обмеження є споконвічне видалення вторинних рядків, а потім вже первинної, на яку вони посилаються Така технологія має назву каскадного видалення У великих базах даних такий каскад може містити безліч рівнів

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

На щастя, у версії SQL Server 2000 каскадні видалення запропоновані як функція зовнішнього ключа Каскадні видалення можна включити в Management Studio (мал 162), а також з допомогою коду T-SQL

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

CREATE TABLE dboEvent_mm_Guide (

EventGuidelD

INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED, EventID

INT NOT NULL

FOREIGN KEY REFERENCES dboEvent ON DELETE CASCADE, GuidelD

INT NOT NULL

FOREIGN KEY REFERENCES dboGuide ON DELETE CASCADE, LastName

VARCHAR(50) NOT NULL,

)

ON [Primary]

Puc 162 Налаштування в Management Studio зовнішніх ключів для каскадного видалення

Слід особливо відзначити, що каскадні видалення і навіть посилальна цілісність підходять не до всіх відносин Все залежить від сталості вторинних рядків Якщо видалення первинної рядка робить вторинну безглуздою, то каскадне видалення має сенс У той же час, якщо вторинна рядок зберігає своє призначення навіть після видалення первинної, каскадне видалення і засоби підтримки посилальної цілісності тільки зруйнують звязок бази даних з реальністю

Як приклад такої ситуації можна навести операцію видалення туру з бази даних Cape Hatteras Adventures У цьому випадку всі події, повязані з туром, втрачають сенс, так само як і відносяться до нього рядка сполучних таблиць (між подією і замовником і між подією і гідом)

Будь-який тур повинен мати базовий табір, так що для таблиці Tour потрібно підтримку посилальної цілісності У той же час, якщо видаляється небудь базовий табір, тур, що починається в ньому, повинен залишитися доступним (він може бути призначений іншому базового табору) Таким чином, каскадне видалення від базового табору до туру не обгрунтовано

Якщо існує підтримка посилальної цілісності, а каскадні видалення відключені, то базовий табір не може бути видалений, поки всі його тури або не будуть видалені, або не будуть призначені іншого табору

Альтернативи фізичній видаленню даних

Багато розробники намагаються уникати видалення даних Замість цього вони створюють систему видалення даних з користувацьких подань, залишаючи їх у вихідних таблицях Це можна реалізувати декількома способами

■ У рядку може існувати логічний бітовий прапорець, який вказує на те, що рядок вилучена Такий підхід спрощує видалення і відновлення одного рядка – для цього потрібно всього лише змінити одне бітове значення Але оскільки реляційна база даних містить безліч повязаних між собою таблиць, роботи виявляється більше, ніж здається на перший погляд Всі запити повинні перевіряти наявність прапора логічного видалення і фільтрувати відповідним чином рядка До того ж, так як рядки залишаються фізично існувати в SQL Server, а система підтримки посилальної цілісності сервера взагалі не знає про існування прапора видалення, може знадобитися підтримка цілісності на програмному рівні Її складність залежить від того, наскільки далеко ви хочете розповсюдити логічне видалення Цей метод прискорює операції логічного видалення, проте сильно уповільнює виконання відбору записів Каскадне логічне видалення може стати дуже складним, а відновлення записів – взагалі перетворитися на кошмар

■ Ще одна альтернатива зводиться до фізичного архівуванню вилучених рядків в іншу таблицю або базу даних Цей метод добре реалізується за допомогою збережених процедур, які вставляють віддалені рядки в архів, а потім видаляють їх з основної бази даних

Цей метод має ряд достоїнств Дані фізично видаляються з основної бази даних, так що відпадає необхідність штучно змінювати інструкції SELECT Використовуючи розподілені подання або федеративну схему бази даних, можна спростити включення в запити відбору інформації з декількох баз даних Фізичне видалення даних дозволяє без перешкод працювати системі підтримки посилальної цілісності До цього слід додати, що основна база даних не засмічується непотрібною інформацією Витяг архівних даних залишається при цьому простим, хоча на адміністратора підтримка архіву покладає додаткове навантаження

Додаткова У главі 53 ви більше дізнаєтесь про розподілених представлених, а вся частина V інформація буде присвячена стратегіям підтримки сховищ даних і доставки інфор-^ ції із них кінцевим користувачам

■ Найбільш досконалою альтернативою видалення рядків є підтримка повного аудиту операцій модифікації даних Це дозволить не тільки відстежити повний ланцюжок оновлень, а й відновити вилучені рядки Водночас ведення аудиту спричиняє появу накладних витрат, повязаних зі складністю, продуктивністю і обсягом збережених даних

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

“. – Вірующіе дані і виконують логічне видалення рядків

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*