C, PHP, VB, .NET

Дневникът на Филип Петров


* Заявки Delete

Публикувано на 06 април 2009 в раздел Бази от Данни.

Подобно на INSERT, заявките от тип DELETE са с изключително прост синтаксис:

DELETE FROM <таблица>
WHERE <условие>;

Нека демонстрираме един пример с базата от данни "banks". Нека видим първо списък на акаунтите:

mysql> SELECT * FROM accounts;
+----+----------+------+-----------+-------------+
| id | amount   | type | branch_id | customer_id |
+----+----------+------+-----------+-------------+
|  1 |   156.38 |    2 |         1 |           1 |
|  2 |   136.22 |    1 |         1 |           2 |
|  3 |    42.98 |    1 |         1 |           3 |
|  4 |  1236.33 |    1 |         1 |           4 |
|  5 |   211.98 |    2 |         1 |           5 |
|  6 |  1200.00 |    2 |         2 |           6 |
|  7 |   133.48 |    1 |         2 |           7 |
|  8 |   256.41 |    2 |         2 |           8 |
|  9 |  1331.50 |    2 |         2 |           9 |
| 10 |   116.88 |    2 |         2 |          10 |
| 11 |   200.91 |    1 |         2 |          10 |
| 12 |    99.18 |    1 |         2 |          11 |
| 13 |  6712.52 |    1 |         3 |          12 |
| 14 | 12000.56 |    1 |         3 |          12 |
| 15 |   322.99 |    2 |         3 |          12 |
| 16 |   991.63 |    1 |         3 |          13 |
| 17 |   559.32 |    2 |         3 |          14 |
| 18 |   680.13 |    1 |         3 |          15 |
| 19 |   532.57 |    1 |         3 |          15 |
| 20 |   402.26 |    1 |         3 |          16 |
| 21 |  1536.91 |    2 |         4 |          17 |
| 22 | 14921.43 |    1 |         4 |          18 |
| 23 |  3910.50 |    1 |         5 |          19 |
| 24 |   231.37 |    1 |         5 |          20 |
| 25 |  7236.60 |    1 |         5 |          21 |
| 26 |  2226.63 |    2 |         5 |          21 |
| 27 |   500.00 |    2 |         6 |          22 |
+----+----------+------+-----------+-------------+
27 rows in set (0.00 sec)

Нека изтрием акаунт с id=26:

DELETE FROM accounts
WHERE id = 26;

Ако изпълните заявката SELECT отново ще видите, че въпросният ред е изчезнал.

Важно е да се знае, че при използването на FOREIGN KEYS и ON DELETE CASCADE ще бъдат изтрити и записите на редове в таблици, които "сочат" към записа, който ще бъде изтрит. Например нека видим таблицата "customers":

mysql> SELECT * FROM customers;
+----+-------------------+---------+----------+
| id | name              | address | bank_mgr |
+----+-------------------+---------+----------+
|  1 | Todor Ivanov      | NULL    |        1 |
|  2 | Petko Stoianov    | NULL    |        1 |
|  3 | Neno Nenov        | NULL    |        2 |
|  4 | Mariana Zaharieva | NULL    |        3 |
|  5 | Elica Zaharieva   | NULL    |        3 |
|  6 | Atanas Petrov     | NULL    |        4 |
|  7 | Ivan Ivanov       | NULL    |        4 |
|  8 | Zlatomir Petrov   | NULL    |        4 |
|  9 | Mihail Ivchev     | NULL    |        5 |
| 10 | Todor Shtilianov  | NULL    |        6 |
| 11 | Ivailo Ivanov     | NULL    |        7 |
| 12 | George Lucas      | NULL    |        8 |
| 13 | George Harison    | NULL    |        8 |
| 14 | Michael Jackson   | NULL    |        8 |
| 15 | Tony Martin       | NULL    |        8 |
| 16 | Tony McCarter     | NULL    |       10 |
| 17 | Alexander Smith   | NULL    |       11 |
| 18 | Maria Smith       | NULL    |       11 |
| 19 | Alain Delrick     | NULL    |       12 |
| 20 | Devry Henry       | NULL    |       12 |
| 21 | Lenard Renne      | NULL    |       12 |
| 22 | Fontaine Rupert   | NULL    |       13 |
+----+-------------------+---------+----------+

Нека изтрием клиент с id 10:

DELETE FROM customers
WHERE id = 10;

Ще видите, че в редовете в таблица "accounts", чието поле "customer_id" е било равно на 10, също са изтрити:

mysql> SELECT * FROM accounts;
+----+----------+------+-----------+-------------+
| id | amount   | type | branch_id | customer_id |
+----+----------+------+-----------+-------------+
|  1 |   156.38 |    2 |         1 |           1 |
|  2 |   136.22 |    1 |         1 |           2 |
|  3 |    42.98 |    1 |         1 |           3 |
|  4 |  1236.33 |    1 |         1 |           4 |
|  5 |   211.98 |    2 |         1 |           5 |
|  6 |  1200.00 |    2 |         2 |           6 |
|  7 |   133.48 |    1 |         2 |           7 |
|  8 |   256.41 |    2 |         2 |           8 |
|  9 |  1331.50 |    2 |         2 |           9 |
| 12 |    99.18 |    1 |         2 |          11 |
| 13 |  6712.52 |    1 |         3 |          12 |
| 14 | 12000.56 |    1 |         3 |          12 |
| 15 |   322.99 |    2 |         3 |          12 |
| 16 |   991.63 |    1 |         3 |          13 |
| 17 |   559.32 |    2 |         3 |          14 |
| 18 |   680.13 |    1 |         3 |          15 |
| 19 |   532.57 |    1 |         3 |          15 |
| 20 |   402.26 |    1 |         3 |          16 |
| 21 |  1536.91 |    2 |         4 |          17 |
| 22 | 14921.43 |    1 |         4 |          18 |
| 23 |  3910.50 |    1 |         5 |          19 |
| 24 |   231.37 |    1 |         5 |          20 |
| 25 |  7236.60 |    1 |         5 |          21 |
| 27 |   500.00 |    2 |         6 |          22 |
+----+----------+------+-----------+-------------+
24 rows in set (0.00 sec)

Виждате, че акаунти с id 10 и 11 са изтрити.

Това може да доведе до някои "неудобства". Нека например се опитаме да изтрием служител с id = 2:

mysql> DELETE FROM employees
    -> WHERE id = 2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai
nt fails (`banks`.`customers`, CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`bank_
mgr`) REFERENCES `employees` (`id`))

Проблемът тук е, че в таблицата "customers" има FOREIGN KEY "bank_mgr", който сочи към таблицата "employees", но НЯМА ON DELETE CASCADE. Това всъщност е съвсем нормално, защото ако уволним даден служител не би следвало да прекратяваме договорите с клиентите на банката, които той обслужва. Как тогава все пак да изтрием служител с id = 2?

Отговорът е, че трябва да направим NULL или да променим към друг bank_mgr всички клиенти, които сочат към bank_mgr=2:

UPDATE customers
SET bank_mgr = NULL
WHERE bank_mgr = 2;

Вече можем да изтрием служител с id = 2:

mysql> DELETE FROM employees
    -> WHERE id = 2;
Query OK, 1 row affected (0.08 sec)

Въпреки, че първоначалното впечатление е, че FOREIGN KEYS ни "пречат" с тази си особеност, това всъщност ни помага значително, защото така се грижим да правилен интегритет на базата от данни. Не би трябвало в нашата база от данни да има неверни данни, нали?

Тук отново трябва да се обърне изключително внимание на дизайна на базата от данни. Хубаво е в ER диаграмата да си отбелязвате изрично коя връзка има ON DELETE CASCADE и коя не. Винаги преди изпълнение на DELETE трябва да прецените засегнатите връзки и ако има такава без ON DELETE CASCADE, то трябва първо да изпълните заявка от тип UPDATE.

В заключение ще кажем, че е възможно да си спестим този труд, като направим външния ключ с опция "ON DELETE SET NULL". Това всъщност ще свърши абсолютно същата работа, която демонстрирахме по-горе със заявката UPDATE (то естествено беше с обучителна цел). Препоръчително е да се възползвате от тези удобства.

Задача: Изтрийте банка с code = 2

Упътване: В случая не трябва просто да нулирате записите на външния ключ на customers, а трябва и да изтриете редовете. Предполага се, че когато банка бъде изтрита ще бъдат изтрити и нейните клиенти.

 



6 коментара


  1. UPDATE customers
    SET bank_mgr=NULL

    WHERE customers.bank_mgr IN (
    SELECT id
    FROM employees
    WHERE employees.branch_id IN (
    SELECT id
    FROM branches
    WHERE branches.bank_code = (
    SELECT code
    FROM banks
    Where banks.code=2)
    )
    );

    DELETE FROM banks, customers
    WHERE banks.code=2 AND customers.bank_mgr=NULL;

    как ви се струва това като опит за решение на задачата в края на статията ?

    поздрави!

  2. Как си убеден, че няма банка с код различен от 2, в която да няма клиенти с bank_mgr различно от NULL? Аз в упътването написах точно това да НЕ се прави, което вие сте направил.

    Първо трябва да се изтрият клиентите на банката, а след това самата банка. Две заявки DELETE. Това е решението на задачата.

  3. значи просто заменям думата UPDATE c
    DELETE FROM customers
    WHERE...
    натам условието остава както съм го написал и след това още една DELETE операция за банката :)

    между другото откровено казано и аз помислих за забележката, която сте ми написал, но след това проверих в лекцията * Агрегатни функции * и видях, че всички клиенти всъщност имат Bank_mgr поле различно от NULL, но да -- осъзнавам, че в общия случай бих изтрил по погрешка и други клиенти :)

    Благодаря все пак за доразяснението !

  4. това ли е решението на задачата ?

    delete from customers
    where bank_mgr in(
    select id
    from employees
    where branch_id in (
    select id from branches
    where bank_code=2));

    delete from banks
    where code=2;

  5. може ли да демонстрирате как става създаването на таблица при “ON DELETE SET NULL” защото при мене дава ERROR 1005

Добави коментар

Адресът на електронната поща няма да се публикува


*