Foreign key

Topik ini membahas foreign key, yaitu constraint yang menjaga agar rujukan antartabel selalu menunjuk baris yang sungguh ada. Pembahasan mencakup deklarasi REFERENCES, empat pilihan perilaku ON DELETE, dan kriteria memilihnya. Dataset utama memakai foreign key pada tiga tempat, dan topik ini menunjukkan perilakunya pada tabel latihan yang ringkas.

Rujukan yang dijaga server#

Foreign key dideklarasikan dengan kata REFERENCES yang menunjuk kunci utama tabel lain, dan server menolak setiap nilai yang tidak ada di tabel rujukannya. Pada dataset utama, buku.kategori_id merujuk kategori(id), sehingga buku tanpa kategori yang sah mustahil ada.

# foreign key menolak rujukan yang tidak ada
psql -U postgres -d perpustakaan <<'SQL'
CREATE TABLE kategori_uji (id SERIAL PRIMARY KEY, nama TEXT NOT NULL);
CREATE TABLE buku_uji (
    id          SERIAL PRIMARY KEY,
    judul       TEXT NOT NULL,
    kategori_id INTEGER REFERENCES kategori_uji(id)
);
INSERT INTO kategori_uji (nama) VALUES ('Pemrograman');
INSERT INTO buku_uji (judul, kategori_id) VALUES ('Dasar Basis Data', 1);
INSERT INTO buku_uji (judul, kategori_id) VALUES ('Buku Yatim', 99);
DROP TABLE buku_uji;
DROP TABLE kategori_uji;
SQL

CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
ERROR:  insert or update on table "buku_uji" violates foreign key constraint "buku_uji_kategori_id_fkey"
DETAIL:  Key (kategori_id)=(99) is not present in table "kategori_uji".
DROP TABLE
DROP TABLE

INSERT kedua berhasil karena kategori bernomor 1 ada, sedangkan INSERT ketiga ditolak dengan pesan yang menyebut constraint dan nilai yang bermasalah. Perhatikan bahwa penolakan terjadi saat penulisan, bukan saat pembacaan, sehingga kekacauan tidak pernah sempat masuk.

Perilaku ON DELETE#

Pertanyaan yang menentukan adalah apa yang terjadi ketika baris yang dirujuk dihapus. Empat jawaban tersedia, yaitu NO ACTION yang menolak penghapusan, RESTRICT yang menolak tanpa menunggu akhir transaksi, CASCADE yang menghapus baris yang merujuk, dan SET NULL atau SET DEFAULT yang memutus rujukan dengan mengosongkan kolomnya. Tanpa deklarasi, perilakunya NO ACTION.

# CASCADE menghapus buku beserta salinannya
psql -U postgres -d perpustakaan <<'SQL'
CREATE TABLE kategori_uji (id SERIAL PRIMARY KEY, nama TEXT NOT NULL);
CREATE TABLE buku_uji (
    id          SERIAL PRIMARY KEY,
    judul       TEXT NOT NULL,
    kategori_id INTEGER NOT NULL REFERENCES kategori_uji(id) ON DELETE CASCADE
);
INSERT INTO kategori_uji (nama) VALUES ('Sains');
INSERT INTO buku_uji (judul, kategori_id) VALUES ('Fisika Dasar', 1), ('Kimia Dasar', 1);
DELETE FROM kategori_uji WHERE id = 1;
SELECT COUNT(*) AS buku_tersisa FROM buku_uji;
DROP TABLE buku_uji;
DROP TABLE kategori_uji;
SQL

CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 2
DELETE 1
 buku_tersisa
--------------
            0
(1 row)

DROP TABLE
DROP TABLE

Penghapusan satu kategori menghapus dua buku sekaligus tanpa perintah tambahan, dan COUNT menegaskan hasilnya. Perilaku SET NULL menuntut kolom yang boleh kosong, sehingga kombinasi NOT NULL dengan ON DELETE SET NULL ditolak saat deklarasi.

Memilih perilaku dengan sadar#

Pilihan ON DELETE adalah keputusan desain, bukan setelan default yang dibiarkan. Tabel berikut merangkum kriteria umumnya.

Untuk dataset perpustakaan, menghapus kategori tidak seharusnya menghapus koleksi buku, sehingga rujukan buku ke kategori dipakai tanpa CASCADE. Sebaliknya, detail pesanan memang lahir dan mati bersama pesanannya, sehingga CASCADE tepat di sana.

ON UPDATE dan integritas dua arah#

ON UPDATE mengatur reaksi terhadap perubahan nilai kunci yang dirujuk, dan kebutuhannya langka pada kunci surrogat yang tidak pernah berubah. Server juga mencegah perubahan yang melumpuhkan rujukan, sehingga nilai kunci utama yang sedang dirujuk tidak dapat diubah sembarangan tanpa deklarasi khusus.

Integritas bekerja dua arah, yaitu penulisan anak diperiksa terhadap induk, dan penghapusan atau perubahan induk diperiksa terhadap anak. Kedua arah ini menutup celah yang tidak terlihat oleh validasi aplikasi, termasuk penghapusan yang dilakukan oleh alat pemeliharaan di luar jalur aplikasi.

Kesalahan umum#

Kesalahan pertama adalah memasang CASCADE pada rujukan yang seharusnya dijaga, sehingga satu DELETE yang keliru menghapus ratusan baris turunan tanpa konfirmasi. Kesalahan kedua adalah menyimpan id tanpa mendeklarasikan foreign key karena dianggap ringan, padahal biaya pemeriksaannya kecil dan justru menghemat pekerjaan pembersihan data yang tidak konsisten.

Kesalahan ketiga adalah mengira NO ACTION dan RESTRICT identik. Keduanya menolak, tetapi RESTRICT menolak seketika, sedangkan NO ACTION menunggu akhir transaksi, sehingga memungkinkan sementara menukar baris induk dalam transaksi yang sama. Kesalahan keempat adalah membuat tabel tanpa index pada kolom foreign key, karena pemeriksaan penghapusan induk menjadi pemindaian penuh, dan indexnya ditambahkan secara eksplisit karena kunci asing tidak otomatis membuatnya.

Ringkasan#

• REFERENCES menjaga agar setiap nilai rujukan benar-benar ada, dan penulisan yang tidak sah ditolak saat terjadi.
• Perilaku ON DELETE menentukan nasib baris penunjuk, yaitu ditolak, ikut terhapus, dikosongkan, atau diisi default.
• CASCADE hanya untuk relasi yang baris turunannya memang bagian dari induknya, dan rujukan fakta penting dipertahankan dengan NO ACTION.
• Kolom foreign key membutuhkan index eksplisit agar penghapusan induk tidak menjadi pemindaian penuh.

Referensi#

• Foreign Keys — PostgreSQL Documentation — Deklarasi foreign key dan seluruh pilihan referensial.
• Tutorial: Foreign Keys — PostgreSQL Documentation — Bagian tutorial resmi tentang kunci asing.
• CREATE TABLE — PostgreSQL Documentation — Sintaks REFERENCES beserta ON DELETE dan ON UPDATE.