Odpowiedź

DELETE i TRUNCATE — oba narzędzia do czyszczenia tabel, ale działają różnie:

• DELETE usuwa wiersze na podstawie warunku lub wszystkie, obsługuje WHERE, można cofnąć, aktywuje triggery. Może być wolne przy dużej liczbie wierszy (usuwane po jednym wierszu).
• TRUNCATE natychmiastowo czyści całą tabelę, nie można używać WHERE, często nie rejestruje po wierszu, nie zawsze aktywuje triggery. W większości przypadków działanie jest nieodwracalne, nie można cofnąć w niektórych DBMS.

Szczegóły:

• DELETE wspiera cofnięcie transakcji (ROLLBACK), TRUNCATE — nie lub zależy od DBMS.
• TRUNCATE zazwyczaj resetuje licznik identity/sequence.
• TRUNCATE nie jest możliwy, jeśli istnieją klucze obce lub odniesienia do tabeli.

Przykład:

-- Usunąć zamówienia starsze niż 3 lata
DELETE FROM orders WHERE created_at < CURRENT_DATE - INTERVAL '3 years';

-- Całkowicie wyczyścić tabelę (szybko)
TRUNCATE TABLE logs;

Pytanie z pułapką

Czy można odzyskać dane po TRUNCATE?

Typowy błąd: sądzą, że tak jak DELETE, TRUNCATE można cofnąć w transakcji. Ale TRUNCATE zazwyczaj nie można cofnąć ani zrobić UNDO, z wyjątkiem niektórych DBMS (na przykład PostgreSQL podczas pracy w obrębie transakcji).

Przykład (w większości DBMS):

BEGIN;
TRUNCATE TABLE orders;
ROLLBACK; -- W większości DBMS nie zadziała, dane utracone!

Przykłady rzeczywistych błędów z powodu nieznajomości szczegółów tematu

---

Historia

Usunięcie danych tymczasowych w bazie produkcyjnej TRUNCATE zamiast TEST. Trigger audytu usunięcia wierszy zadziałał tylko na DELETE — brak informacji o utraconych danych, czasie i użytkowniku. Utrudniona analiza incydentu.

---

Historia

Czyszczenie tabeli TRUNCATE przy obecności klucza obcego. SQL wygenerował błąd z powodu ograniczenia integralności referencyjnej, skrypt się nie zakończył, automatyzacja zatrzymała się. Rozwiązanie: tymczasowo usunąć klucze obce lub użyć DELETE.

---

Historia

Masowe usuwanie danych przez DELETE w dużych tabelach bez batched: z powodu długich blokad baza "zawisła", inne transakcje czekały na zwolnienie blokad. Efekt — awaria systemu. Poprawnie: używać DELETE LIMIT/OFFSET, jeśli jest wspierane, lub batched.