DML – delete – SQL – usuwanie danych z bazdy danych

Usuwanie danych z bazy to w systemach transakcyjnych stosunkowo najrzadziej wykonywana operacja. Przez wiele osób uznawana za niebezpieczną. Jednak dobrze zaprojektowana baza nie pozwoli nam usunąć rzeczy, które mogą zakłócić jej działanie. Nie mniej przed usuwaniem danych warto zapisać tabele lub co najmniej wiersze które chcemy usunąć. Dobrym zwyczajem może być napisanie selecta z takimi warunkami z jakimi chcemy usuwać by sprawdzić czy wybraliśmy tylko te dane, które rzeczywiście chcemy usunąć. W systemach Oraclowych mamy automatyczną transakcję więc możemy podejrzeć zmiany które dokonaliśmy przed skomitowaniem. W innych systemach ręczne otwarcie transakcji może być dodatkowym zabezpieczeniem przed nie przewidywaną utratą danych.

Jeśli chcemy usunąć wszystkie wiersze z tabeli należy rozważyć operację TRUNCATE. Truncate wycina wszystko z tabeli. Dodatkowym atutem jest cofnięcie HWM, High Water Mark – czyli wskaźnik końca pliku (wskaźnik wysokiej wody). Jest przesuwany w przód w momencie dodawania danych do tabeli. Jednak nigdy nie jest cofany. Mimo usuwania danych tabela wciąż rośnie. Dlatego projektując np tabele interfejsowe warto pomyśleć o okresowym cofnięciu tego znacznika. W różnych RDBMS jest różnie realizowane(bądź w ogóle nie jest w inny sposób niż truncate).

Tak więc składnia truncate: truncate table nazwa tabeli;

truncate table przyjaciele;

spowoduje usunięcie wszystkich danych z tabeli przyjaciele.

Ważna uwaga odnośnie truncate: To nie jest DML – transakcja!. Tak więc nie będzie możliwy rollback! Jeśli zrobiliśmy kopie tabeli przed usuwaniem bądź modyfikowaniem danych po zakończonej operacji należy posprzątać. Tabele można usunąć poleceniem DROP, które jest częścią DDL SQL.

Nie mniej składnia operacja usuwania tabeli wygląda następująco:

drop table nazwa;

czyli:

drop table przyjaciele_kopia;

usunie nam tabele przyjaciele_kopia.Podobnie jak Truncate DROP nie jest opracją DML więc nie będzie możliwe usuwanie danych. Przejdźmy do meritum. Polecenie delete pozwala nam usuwać dane, wiersze z tabeli. Możemy usuwać tym poleceniem tylko całe wiersze więc nie podajemy nazw kolumn do usunięcia. składnia polecenia delete wygląda następująco:

delete from tabela;

Powyższe polecenie wyczyści całą tabele i było by równoznaczne z truncate gdyby nie fakt iż nie cofa HWM.

delete from tabele where kolumna_id =1;

spowoduje usunięcie wierszy spełniających warunek. Więcej o warunkach można przeczytać w części poświęconej filtrowaniu danych. Na realnym przykładzie, chcąc usunąć z naszej przykładowej tabeli przyjaciół wszystkie kobiety należy wykonać polecenie:

delete from przyjaciele where plec=0;

Jeśli chcemy usunąć przyjaciela o id=34

delete from przyjaciele where id=34;

W warunkach do usuwania możemy posługiwać się wszystkimi możliwościami jakie dają filtry (funkcje,podzapytania).

Teraz trochę o niespodziankach jakie mogą nas spotkać. Jeśli tabela jest relacją do innej tabeli, weźmy pod uwagę przykład nagłówka i pozycji faktury. Niedopuszczalną sytuacją jest posiadanie pozycji faktury do których nie ma nagłówka. Więc często podczas zestawiania klucza głównego i obcego ustawia się usuwanie kaskadowe, czyli wszystkie podrzędne klucze zostaną też usunięte. Usuwając jeden wiersz z tabeli nagłówków możemy usunąć kilka tysięcy wierszy z tabeli pozycji z jednym razem zupełnie nie świadomie.

Czasem dla mniej ważnych relacji ustawia się w momencie usuwania klucza głównego wartości null dla klucz obcych. W przykładzie o fakturach możemy np mieć w nagłówku referencje do klucza głównego klienta. Usuwamy dane klienta i traci się powiązanie. Jest bardziej optymistycznie niż w poprzednim przykładzie bo faktura zostanie tylko znikną nam dane klienta. Przykład i tak jest niezgodny z literą prawa. W przypadku większych systemów warto sprawdzić constrainty tabeli z której chcemy usuwać dane. Jest to tym bardziej potrzebne jeśli operujemy na systemach w których istotne jest księgowanie albo inne procesy wymagane prawem. Jak widać kopia tabeli może nie zawsze się przydać ze względu na kaskadowe usuwanie.

Jest też trzecia możliwość związana z kluczami. To po prostu brak akcji na usunięcie klucza głównego. W Oracle nic nie szkodzi, bo sekwencja się nie cofnie, i powstałe dziury nie są uzupełniane. Jednak w MYSQL w jednym z silników po  restarcie bazy następuje cofnięcie wartości do maksymalnej +1 z tabeli. Wyobraźmy sobie sytuacje w której usuwamy ostatnio dodanego klienta i następuje restart bazy, mija trochę czasu i ktoś dodaje innego klienta. I w ten sposób możemy mieć złego klienta przypisanego do faktury. Numer id został przypisany innej osobie.

Kolejna niespodzianką przy usuwaniu to tak jak przy innych DML’ach mogą być triggery. Czyli wyzwalacze o których wspomniałem przy okazji insertowania danych. Wyzwalacze mogą nie pozwolić na usunięcie danych lub zmodyfikować nasze zapytanie( w sensie wynik delete może być inny od oczekiwanego).

Jeszcze informacja dla osób początkujących skopiowanie tabeli przez select into tudzież create as nie kopiuje constraintów i triggerów więc wynik przetestowanego zapytania DML na kopii może być zupełnie inny niż na tabeli produkcyjnej.

Leave a Reply

Your email address will not be published. Required fields are marked *

one × 4 =