Batasan Foreign Key dan Aksi Referensial (ON DELETE / ON UPDATE)

Data yang akan kita pakai — category dan item

Batasan foreign key (batasan yang menjamin nilai kolom selalu ada sebagai primary key di tabel lain) adalah mekanisme yang melindungi integritas referensial antar tabel (keadaan di mana setiap baris yang dirujuk benar-benar ada).

Ia memblokir kontradiksi seperti "item menunjuk ke category yang tidak ada" atau "menghapus category meninggalkan itemnya menggantung", di lapisan database.

Sebelum latihan, lihat definisi kolom dan data sampel dari kedua tabel — category dan item.

① Jalankan PRAGMA table_info(category); dan PRAGMA table_info(item); untuk memeriksa definisi kolom.

② Pratinjau semua baris dengan SELECT * FROM category; dan SELECT * FROM item;.

③ Jalankan PRAGMA foreign_keys; untuk melihat keadaan pemeriksaan foreign-key saat ini (dikembalikan sebagai angka).

Pemeriksaan foreign-key diaktifkan dengan PRAGMA foreign_keys=ON

Batasan foreign key dideklarasikan dengan menulis REFERENCES tabel_parent(kolom_parent) di definisi kolom tabel child.

item.cat_id membawa REFERENCES category(cat_id), yang mengkodekan aturan: setiap nilai item.cat_id harus ada di category.

Namun, di konsol kursus ini pemeriksaan foreign-key secara default off (PRAGMA foreign_keys bernilai 0).

Selama off, `REFERENCES` sebetulnya tidak ditegakkan dan baris yang menunjuk ke category yang tidak ada bisa di-insert.

Untuk benar-benar menegakkan batasannya, jalankan PRAGMA foreign_keys=ON; sekali per koneksi.

-- Aktifkan pemeriksaan foreign-key
PRAGMA foreign_keys=ON;

-- Konfirmasi keadaan (1 berarti aktif)
PRAGMA foreign_keys;

-- Coba insert baris yang menunjuk ke cat_id=99 (tidak ada di category)
-- Dengan foreign_keys=ON, ini ditolak dengan FOREIGN KEY constraint failed
INSERT INTO item VALUES (5,'Mouse',99);

Bayangkan kebutuhannya: "biarkan database memblokir kesalahan pendaftaran di mana item merujuk ke category yang tidak ada." Dengan pemeriksaan foreign-key aktif, kamu akan sengaja meng-insert baris yang tidak konsisten dan menyaksikannya error. (Jalankan dengan benar dan penjelasannya muncul.)

① Di baris 1, jalankan PRAGMA foreign_keys=ON; untuk mengaktifkan pemeriksaan foreign-key.

② Lalu INSERT sebuah baris ke item dengan item_id 5, item_name Mouse, dan cat_id 99 — nilai yang tidak ada di category. INSERT ini melanggar batasan foreign-key, jadi error adalah hasil yang benar.

ON DELETE — apa yang harus dilakukan terhadap baris child ketika baris parent dihapus

Ketika baris parent (category) dihapus, apa yang harus terjadi pada baris child (item) yang menunjuk ke sana? Kamu memutuskannya dengan menulis aksi referensial di klausa REFERENCES.

Ada tiga aksi referensial yang umum.

ON DELETE CASCADE berarti menghapus parent juga menghapus child (cascade delete), ON DELETE SET NULL berarti kolom referensi child diset menjadi NULL dan barisnya disimpan, dan ON DELETE RESTRICT berarti delete parent itu sendiri ditolak selama child masih merujuknya.

Kalau kamu tidak menentukan aksi, defaultnya juga mirip RESTRICT (parent yang dirujuk tidak bisa dihapus).

Perilaku mana yang sesuai bergantung pada kebutuhan bisnis.

-- Contoh deklarasi dengan ON DELETE SET NULL
CREATE TABLE cat_demo(cat_id INTEGER PRIMARY KEY, cat_name TEXT NOT NULL);
CREATE TABLE item_demo(
  item_id INTEGER PRIMARY KEY,
  item_name TEXT NOT NULL,
  cat_id INTEGER REFERENCES cat_demo(cat_id) ON DELETE SET NULL
);
INSERT INTO cat_demo VALUES (1,'Stationery'),(2,'Electronics');
INSERT INTO item_demo VALUES (1,'Pen',1),(2,'Phone',2),(3,'Cable',2);

-- Menghapus parent cat_id=2 mengubah cat_id Phone / Cable menjadi NULL
DELETE FROM cat_demo WHERE cat_id=2;
SELECT item_id, item_name, cat_id FROM item_demo ORDER BY item_id;

Bayangkan kebutuhannya: "ketika category dihapus, juga hapus setiap item yang termasuk di dalamnya." Bangun parent/child mandiri dengan ON DELETE CASCADE dan saksikan delete parent merembet ke child.

① Aktifkan pemeriksaan foreign-key, lalu DROP TABLE IF EXISTS dan buat ulang cat_x (cat_id, cat_name) dan item_x (item_id, item_name, cat_id) di mana item_x.cat_id punya foreign key ON DELETE CASCADE ke cat_x.

② Insert 2 baris ke cat_x (Stationery / Electronics) dan 3 baris ke item_x (satu dengan cat_id 1, dua dengan cat_id 2).

③ Hapus category dengan cat_id 2 dari cat_x, lalu SELECT semua baris dari item_x dan konfirmasi item yang menunjuk ke cat_id=2 ikut terhapus secara cascade.

SET NULL dan RESTRICT — pilih antara menyimpan dan menolak

ON DELETE SET NULL menyimpan baris child ketika parent dihapus, hanya menggantikan kolom yang merujuk dengan NULL.

Pakai ketika kamu tidak ingin menghapus record child itu sendiri, seperti "simpan item sebagai 'tanpa kategori'."

Kalau kolom referensi child adalah NOT NULL, NULL tidak bisa ditulis, jadi kolom yang dipakai dengan SET NULL harus mengizinkan NULL.

ON DELETE RESTRICT menolak delete parent itu sendiri selama child masih merujuknya.

Pakai ketika kamu ingin fail safe (jangan sengaja menjatuhkan baris master yang masih dirujuk).

Begitu kamu menghapus atau menugaskan ulang semua child sehingga tidak ada referensi tersisa, parent bisa dihapus.

Di latihan berikutnya kamu akan menyiapkan SET NULL dan RESTRICT berdampingan dalam satu konsol untuk membandingkannya.

-- Contoh RESTRICT: parent dengan child tidak bisa dihapus
CREATE TABLE cat_r(cat_id INTEGER PRIMARY KEY, cat_name TEXT NOT NULL);
CREATE TABLE item_r(
  item_id INTEGER PRIMARY KEY,
  item_name TEXT NOT NULL,
  cat_id INTEGER REFERENCES cat_r(cat_id) ON DELETE RESTRICT
);
INSERT INTO cat_r VALUES (1,'Stationery'),(2,'Electronics');
INSERT INTO item_r VALUES (1,'Pen',1);

-- cat_id=1 dirujuk oleh item_r, jadi delete ditolak
DELETE FROM cat_r WHERE cat_id=1;

-- cat_id=2 tidak punya referensi, jadi bisa dihapus
DELETE FROM cat_r WHERE cat_id=2;
SELECT * FROM cat_r ORDER BY cat_id;

Bayangkan kebutuhannya: "kalau kami menghentikan satu category, simpan itemnya sebagai 'tanpa kategori' tetapi jangan sentuh item di category lain sama sekali." Dengan 3 category dan 5 item di bawah ON DELETE SET NULL, kamu akan melihat bahwa hanya child dari parent yang dihentikan yang menjadi NULL sementara yang lain tidak tersentuh.

① Aktifkan pemeriksaan foreign-key, lalu DROP TABLE IF EXISTS dan buat ulang cat_s (cat_id, cat_name) dan item_s (item_id, item_name, cat_id) di mana item_s.cat_id punya foreign key ON DELETE SET NULL ke cat_s. item_s.cat_id harus mengizinkan NULL.

② Insert 3 baris ke cat_s (Office=1 / Gadget=2 / Drink=3) dan 5 baris ke item_s (Marker dan Tape di Office, Tablet dan Charger di Gadget, Coffee di Drink).

③ Hapus hanya Gadget (cat_id=2) dari cat_s, lalu SELECT semua baris dari item_s dan konfirmasi bahwa hanya cat_id Tablet dan Charger yang menjadi NULL sementara item di bawah Office / Drink mempertahankan cat_id aslinya — semua 5 baris masih ada.

Bayangkan kebutuhannya: "cegah penghapusan tidak sengaja dari category yang masih punya banyak item." Bangun parent/child mandiri dengan ON DELETE RESTRICT dan saksikan ia error ketika kamu mencoba menghapus parent yang punya 3 child.

① Aktifkan pemeriksaan foreign-key, lalu DROP TABLE IF EXISTS dan buat ulang cat_t (cat_id, cat_name) dan item_t (item_id, item_name, cat_id) di mana item_t.cat_id punya foreign key ON DELETE RESTRICT ke cat_t.

② Insert Stationery (cat_id 1) ke cat_t, dan 3 item di bawahnya (mis. Pen / Pencil / Notebook) ke item_t.

③ Coba DELETE category dengan cat_id 1 dari cat_t. Karena 3 child masih merujuknya, error adalah hasil yang benar.