TRUNCATE TABLE

Tabel kerja sesekali perlu dikosongkan total: tabel staging sebelum muat data berikutnya, tabel log uji coba, atau tabel latihan seperti milik kita. Kita sudah tahu bahwa DELETE FROM tabel; tanpa WHERE menghapus seluruh baris — tetapi perintah itu menyisakan satu jejak: counter AUTO_INCREMENT tetap berdiri di posisi terakhir, dan penghapusannya baris demi baris. MySQL menyediakan perintah khusus untuk mengosongkan tabel sekaligus: TRUNCATE TABLE. Pada chapter penutup section ini kita akan membuktikan bahwa TRUNCATE benar-benar me-reset penomoran ke 1, memahami statusnya sebagai statement DDL yang tidak bisa dibatalkan ROLLBACK, lalu melihat mengapa tabel yang di-referensikan foreign key menolak untuk di-truncate.

Menyiapkan Tabel Latihan #

Kita siapkan tabel sederhana dengan tiga baris ber-id berurutan sebagai titik awal pengamatan:

CREATE DATABASE belajar_dml;
USE belajar_dml;

Output yang Dihasilkan:

Query OK, 1 row affected (0.00 sec)

Database changed

CREATE TABLE produk (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL
);

INSERT INTO produk (nama) VALUES ('Buku Tulis'), ('Pensil 2B'), ('Penghapus');

SELECT id, nama FROM produk ORDER BY id;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

+----+------------+
| id | nama       |
+----+------------+
|  1 | Buku Tulis |
|  2 | Pensil 2B  |
|  3 | Penghapus  |
+----+------------+
3 rows in set (0.00 sec)

Tiga baris dengan id 1 sampai 3. Angka-angka inilah yang menjadi alat ukur kita: DELETE pada chapter sebelumnya meninggalkan counter di angka 4 — mari kita lihat apa yang dilakukan TRUNCATE.

TRUNCATE Me-reset AUTO_INCREMENT #

Kita jalankan TRUNCATE, isi satu baris baru, lalu baca tabelnya:

TRUNCATE TABLE produk;

INSERT INTO produk (nama) VALUES ('Stipo');

SELECT id, nama FROM produk ORDER BY id;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+----+-------+
| id | nama  |
+----+-------+
|  1 | Stipo |
+----+-------+
1 row in set (0.00 sec)

Baris baru mendapat id 1 — counter AUTO_INCREMENT benar-benar kembali ke awal, sesuatu yang mustahil terjadi lewat DELETE. Alasannya mekanis: TRUNCATE tidak menghapus baris satu per satu, melainkan menjatuhkan dan membuat ulang tabel yang kosong dengan struktur yang sama. Tabel hasil pembuatan ulang itu lahir dengan counter segar dari nilai awal, jadi penomoran dimulai lagi dari 1. Perhatikan juga respons statement-nya: Query OK, 0 rows affected — TRUNCATE tidak pernah melaporkan jumlah baris yang dibuang, karena ia tidak bekerja dengan cara menghitung baris.

Sifat reset inilah yang membuat TRUNCATE menjadi pilihan tepat untuk tabel staging atau tabel yang penomorannya harus mulai bersih di tiap siklus pemuatan. Sebaliknya, pada tabel produksi yang barisnya mungkin masih dirujuk sistem lain, reset penomoran bukan berita baik — id yang dipakai ulang bisa bertabrakan dengan catatan lama di luar database.

TRUNCATE adalah DDL Tanpa WHERE dan Tanpa ROLLBACK #

Sebagai statement DDL (Data Definition Language) bersaudara dengan CREATE TABLE, TRUNCATE tidak mengenal klausa WHERE — ia selalu mengenai seluruh isi tabel, tanpa pengecualian. Konsekuensi yang lebih penting lagi: statement DDL menyebabkan implicit commit. Kita buktikan lewat percobaan transaksi:

BEGIN;

INSERT INTO produk (nama) VALUES ('Kertas HVS A4');

TRUNCATE TABLE produk;

ROLLBACK;

SELECT COUNT(*) AS sisa_baris FROM produk;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+------------+
| sisa_baris |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

Mari kita baca jalannya peristiwa: BEGIN membuka transaksi, INSERT menambah satu baris di dalamnya, lalu TRUNCATE mengosongkan tabel — dan tanpa kita minta, sekaligus meng-commit transaksi yang sedang terbuka. ROLLBACK yang menyusul hanya melaporkan 0 rows affected karena tidak ada lagi transaksi yang bisa dibatalkan. Hasil akhirnya SELECT COUNT(*) menunjukkan 0: baris Kertas HVS A4 tidak bisa dikembalikan. Bandingkan dengan DELETE yang merupakan DML — di dalam transaksi, DELETE bisa dibatalkan penuh dengan ROLLBACK selama belum di-commit.

TRUNCATE dan Foreign Key: ERROR 1701 #

Sifat “jatuhkan lalu buat ulang tabel” menabrak aturan main foreign key: server tidak bisa menjamin hubungan induk-anak pada operasi yang bukan baris-per-baris. Kita bangun pasangan tabel ber-relasi:

CREATE TABLE pelanggan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL
);

CREATE TABLE pesanan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  id_pelanggan INT NOT NULL,
  keterangan VARCHAR(100) NOT NULL,
  FOREIGN KEY (id_pelanggan) REFERENCES pelanggan (id)
);

INSERT INTO pelanggan (nama) VALUES ('Budi');

INSERT INTO pesanan (id_pelanggan, keterangan) VALUES (1, 'Buku Tulis 5 pack');

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Tabel induk pelanggan kini di-referensikan oleh tabel anak pesanan. Kita coba kosongkan si induk:

-- @error
TRUNCATE TABLE pelanggan;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`belajar_dml`.`pesanan`, CONSTRAINT `pesanan_ibfk_1`)
Bye

Galat ERROR 1701 menolak operasi tersebut: tabel yang di-referensikan foreign key dari tabel lain tidak boleh di-truncate. Perhatikan kontrasnya dengan DELETE FROM pelanggan; pada chapter DELETE — statement DML itu ditolak hanya bila masih ada baris anak yang benar-benar menunjuk baris induk tertentu (ERROR 1451), sedangkan TRUNCATE ditolak langsung selama hubungan referensinya ada, apa pun isinya. Arah yang sebaliknya aman: tabel ANAK boleh di-truncate karena tidak ada tabel lain yang bergantung padanya:

TRUNCATE TABLE pesanan;

SELECT id, nama FROM pelanggan ORDER BY id;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

+----+------+
| id | nama |
+----+------+
|  1 | Budi |
+----+------+
1 row in set (0.00 sec)

Tabel pesanan berhasil dikosongkan dengan mulus, sementara pelanggan tetap utuh berisi Budi. Urutan kerja yang benar untuk mengosongkan pasangan tabel ber-relasi adalah dari anak ke induk: kosongkan pesanan dulu, baru pelanggan bisa di-truncate setelah tidak lagi di-referensikan baris mana pun — atau lepas constraint-nya dengan ALTER TABLE terlebih dahulu.

DELETE, TRUNCATE, atau DROP #

Tiga perintah penghapusan yang kita kenal sepanjang course ini punya wilayah kerja masing-masing. Tabel berikut merangkum perbedaan pokoknya:

Membersihkan Database Latihan #

Latihan kita tuntas. Dua tabel tersisa — produk dan pelanggan — ikut terjatuh bersama database latihan:

DROP DATABASE belajar_dml;

Output yang Dihasilkan:

Query OK, 3 rows affected (0.00 sec)

Referensi #

• TRUNCATE TABLE Statement — MySQL 8.4 Reference Manual
• DELETE Statement — MySQL 8.4 Reference Manual
• COMMIT Statement — MySQL 8.4 Reference Manual

Section Manipulasi Data kini tuntas: kita bisa menyisipkan data dalam batch, membacanya dengan SELECT, memperbaruinya dengan UPDATE maupun upsert, menghapusnya secara terarah dengan DELETE, dan mengosongkan tabel total dengan TRUNCATE. Perhatikan satu benang merah di sepanjang section ini: hampir setiap perintah mengandalkan WHERE untuk memilih baris — dan klausa itulah bintang berikutnya. Section Filtering dan Sorting membedah WHERE sampai ke detailnya: operator perbandingan, kombinasi logika AND/OR, pencarian pola LIKE, rentang BETWEEN, hingga pengurutan dan pembatasan hasil dengan ORDER BY serta LIMIT.