* Заявки 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, а трябва и да изтриете редовете. Предполага се, че когато банка бъде изтрита ще бъдат изтрити и нейните клиенти.
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, в която да няма клиенти с bank_mgr различно от NULL? Аз в упътването написах точно това да НЕ се прави, което вие сте направил.
Първо трябва да се изтрият клиентите на банката, а след това самата банка. Две заявки DELETE. Това е решението на задачата.
значи просто заменям думата UPDATE c
DELETE FROM customers
WHERE...
натам условието остава както съм го написал и след това още една DELETE операция за банката :)
между другото откровено казано и аз помислих за забележката, която сте ми написал, но след това проверих в лекцията * Агрегатни функции * и видях, че всички клиенти всъщност имат Bank_mgr поле различно от NULL, но да -- осъзнавам, че в общия случай бих изтрил по погрешка и други клиенти :)
Благодаря все пак за доразяснението !
това ли е решението на задачата ?
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;
Колоната, по която се прави FK, трябва да НЕ Е NOT NULL.
може ли да демонстрирате как става създаването на таблица при “ON DELETE SET NULL” защото при мене дава ERROR 1005