Antwort

DELETE und TRUNCATE – beide Werkzeuge zum Reinigen von Tabellen, aber sie funktionieren unterschiedlich:

• DELETE löscht Zeilen basierend auf einer Bedingung oder alle, unterstützt WHERE, kann zurückgesetzt werden, aktiviert Trigger. Kann langsam sein bei einer großen Anzahl von Zeilen (zeilenweise).
• TRUNCATE löscht sofort die gesamte Tabelle, WHERE kann nicht verwendet werden, wird oft nicht zeilenweise protokolliert, aktiviert nicht immer Trigger. In vielen Fällen ist die Aktion nicht rückgängig zu machen, kann in bestimmten DBMS nicht zurückgesetzt werden.

Details:

• DELETE unterstützt das Rollback der Transaktion (ROLLBACK), TRUNCATE nicht oder hängt von der DBMS ab.
• TRUNCATE setzt normalerweise den Identity/Sequence-Zähler zurück.
• TRUNCATE ist nicht anwendbar, wenn es externe Schlüssel oder Verweise auf die Tabelle gibt.

Beispiel:

-- Bestellungen löschen, die älter als 3 Jahre sind
DELETE FROM orders WHERE created_at < CURRENT_DATE - INTERVAL '3 years';

-- Tabelle vollständig leeren (schnell)
TRUNCATE TABLE logs;

Fangfrage

Kann man Daten nach TRUNCATE wiederherstellen?

Typischer Fehler: man denkt, dass TRUNCATE wie DELETE zurückgesetzt werden kann. Aber TRUNCATE kann in der Regel nicht zurückgesetzt oder rückgängig gemacht werden, außer in einigen DBMS (z.B. PostgreSQL bei der Arbeit innerhalb einer Transaktion).

Beispiel (in den meisten DBMS):

BEGIN;
TRUNCATE TABLE orders;
ROLLBACK; -- In den meisten DBMS funktioniert das nicht, Daten sind verloren!

Beispiele echter Fehler wegen Unkenntnis der Feinheiten des Themas

---

Geschichte

Löschung von temporären Daten in einer Produktionsdatenbank mit TRUNCATE anstelle von TEST. Der Auditing-Trigger für das Löschen von Zeilen wurde nur bei DELETE aktiviert – Informationen über verlorene Daten, Zeit und Benutzer fehlen. Die Analyse des Vorfalls war betroffen.

---

Geschichte

Leeren der Tabelle mit TRUNCATE bei Vorhandensein eines Fremdschlüssels. SQL warf einen Fehler wegen der Einschränkung der referenziellen Integrität, das Skript wurde nicht beendet, die Automatisierung stoppte. Lösung: vorübergehend die Fremdschlüssel entfernen oder DELETE verwenden.

---

Geschichte

Massenlöschung von Daten über DELETE in großen Tabellen ohne Batching: wegen langer Sperren "hing" die Datenbank, andere Transaktionen warteten auf die Freigabe der Sperren. Ergebnis – Ausfall des Systems. Richtig: DELETE LIMIT/OFFSET verwenden, wenn unterstützt, oder Batches verwenden.