Foreign Key di SQLite: REFERENCES, ON DELETE, dan PRAGMA

Foreign Key Adalah Penunjuk Antartabel

Foreign key adalah kolom di satu tabel yang nilainya harus cocok dengan sebuah baris di tabel lain. Inilah cara database relasional menyatakan "baris posts ini milik baris authors itu" tanpa menyalin nama dan email penulis ke setiap post.

Berikut contoh sekecil mungkin, yaitu tabel induk dan tabel anak yang dihubungkan oleh FK:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO authors (name) VALUES ('Ada'), ('Boris');
INSERT INTO posts (title, author_id) VALUES ('Hello', 1), ('Notes', 2);

SELECT p.title, a.name
FROM posts p JOIN authors a ON a.id = p.author_id;

author_id INTEGER REFERENCES authors(id) adalah seluruh deklarasi foreign key-nya. Artinya: kolom ini berisi id dari tabel authors. Database sekarang tahu bahwa kedua tabel saling terkait, dan kalau penegakannya aktif, database akan menolak insert yang menunjuk ke penulis yang tidak ada.

Foreign Key Nonaktif Secara Bawaan

Inilah fakta terpenting tentang foreign key di SQLite, dan fakta ini mengejutkan semua orang: SQLite mem-parse klausa REFERENCES tapi tidak menegakkannya kecuali kamu memintanya. Alasannya adalah kompatibilitas historis, karena database lama dibuat sebelum fitur ini ada.

Lihat apa yang terjadi tanpa penegakan:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

-- author 999 does not exist, but SQLite happily accepts this:
INSERT INTO posts (title, author_id) VALUES ('Orphan', 999);

SELECT * FROM posts;

Baris yatim piatu itu langsung masuk. Untuk mendapatkan perlindungan yang sebenarnya kamu inginkan, jalankan PRAGMA foreign_keys = ON; di awal setiap koneksi:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO posts (title, author_id) VALUES ('Orphan', 999);

Sekarang insert gagal dengan FOREIGN KEY constraint failed. Pragma ini berlaku per koneksi, bukan per database, karena pengaturannya tidak disimpan di file. Setiap aplikasi, setiap sesi CLI, dan setiap fixture pengujian harus mengaturnya. Sebagian besar kode production menjalankan PRAGMA foreign_keys = ON; tepat setelah membuka koneksi.

Apa yang Sebenarnya Dibutuhkan Klausa REFERENCES

Kolom yang kamu rujuk harus berupa PRIMARY KEY atau punya constraint UNIQUE. Dengan begitu, SQLite bisa menjamin pencariannya tidak ambigu. Tipenya juga sebaiknya cocok: SQLite longgar soal tipe, tapi mencampurnya sama saja dengan mengundang kejutan.

Kamu bisa menulis FK dengan dua cara. Langsung pada kolomnya:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    author_id INTEGER REFERENCES authors(id),
    title     TEXT
);

SELECT sql FROM sqlite_master WHERE name = 'posts';

Atau sebagai constraint tingkat tabel yang terpisah, yang wajib dipakai kalau foreign key mencakup beberapa kolom:

CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE tags  (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL,
    tag_id  INTEGER NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (tag_id)  REFERENCES tags(id)
);

SELECT sql FROM sqlite_master WHERE name = 'post_tags';

Kedua bentuk menghasilkan constraint yang sama persis. Pakai yang lebih enak dibaca untuk tabel yang sedang kamu buat.

ON DELETE: Apa yang Terjadi pada Baris Anak

Saat kamu menghapus baris induk, SQLite harus memutuskan apa yang dilakukan pada baris anak yang menunjuk ke sana. Kamu memilih kebijakannya dengan ON DELETE:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('A'), ('B') ON CONFLICT DO NOTHING;
INSERT INTO posts (title, author_id) VALUES ('A', 1), ('B', 1);

DELETE FROM authors WHERE id = 1;
SELECT * FROM posts;

Menghapus Ada juga menghapus kedua post miliknya. Pilihannya:

• CASCADE: hapus juga baris anaknya. Cocok untuk data yang "dimiliki", seperti post di bawah seorang penulis atau item di bawah sebuah pesanan.
• SET NULL: kosongkan kolom FK. Cocok kalau baris anak sebaiknya tetap ada tanpa induk (misalnya komentar dari user yang sudah dihapus menjadi anonim).
• SET DEFAULT: isi kolom FK dengan nilai default yang dideklarasikan.
• RESTRICT: blokir penghapusan kalau masih ada baris anak. Langsung gagal saat pernyataan dijalankan.
• NO ACTION: bawaannya. Secara fungsi mirip dengan RESTRICT di sebagian besar kasus (pemeriksaannya ditunda sampai commit, tapi hasilnya sama: kamu tidak bisa meninggalkan baris anak yang menggantung).

ON UPDATE bekerja dengan cara yang sama untuk perubahan pada key induk, meskipun memperbarui primary key jarang dilakukan.

Foreign Key Constraint Failed: Artinya

Kamu akan melihat error ini dalam dua situasi. Pertama, saat memasukkan atau memperbarui baris anak dengan nilai yang tidak punya induk yang cocok:

sqlite> INSERT INTO posts (title, author_id) VALUES ('Stray', 999);
Runtime error: FOREIGN KEY constraint failed

Entah penulis 999 tidak ada, atau tipe kolomnya tertukar. Masukkan induknya lebih dulu, atau perbaiki nilainya.

Kedua, saat menghapus (atau memperbarui) induk yang masih punya anak, kalau FK-nya memakai RESTRICT atau NO ACTION:

sqlite> DELETE FROM authors WHERE id = 1;
Runtime error: FOREIGN KEY constraint failed

Hapus baris anaknya lebih dulu, atau ubah FK-nya menjadi ON DELETE CASCADE/SET NULL kalau penghapusan berantai memang yang kamu inginkan.

Ada juga kerabatnya yang lebih jarang muncul, yaitu FOREIGN KEY mismatch. Error itu muncul saat kolom yang dirujuk bukan primary key atau unique, atau jumlah kolomnya tidak sesuai. Itu error skema, bukan error data.

Menambahkan Foreign Key ke Tabel yang Sudah Ada

ALTER TABLE di SQLite terbatas: kamu bisa menambahkan kolom dengan foreign key, tapi tidak bisa menempelkan foreign key ke kolom yang sudah ada. Solusi standarnya adalah langkah membangun ulang lalu mengganti nama:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('Hello', 1), ('World', 1);

PRAGMA foreign_keys = OFF;
BEGIN;

CREATE TABLE posts_new (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO posts_new SELECT id, title, author_id FROM posts;

DROP TABLE posts;
ALTER TABLE posts_new RENAME TO posts;

COMMIT;
PRAGMA foreign_keys = ON;

SELECT sql FROM sqlite_master WHERE name = 'posts';

Polanya: matikan penegakan, buat tabel baru dengan constraint yang kamu inginkan, salin datanya, hapus tabel lama, lalu ganti namanya. BEGIN/COMMIT menjaganya tetap atomik. Nyalakan kembali penegakannya di akhir, dan SQLite akan memvalidasi semua baris yang ada terhadap constraint baru. Kalau ada data yang tidak valid, transaksinya sudah telanjur di-commit, jadi periksa lebih dulu kalau kamu khawatir.

Jalankan PRAGMA foreign_key_check; setelah migrasi untuk memastikan tidak ada baris yatim piatu.

Skema yang Realistis

Kalau semuanya digabungkan, berikut skema blog kecil dengan tabel induk, tabel anak, dan tabel penghubung untuk tag many-to-many:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE posts (
    id         INTEGER PRIMARY KEY,
    author_id  INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
    title      TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tags (
    id   INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  INTEGER NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

Ada tiga hal yang perlu diperhatikan. author_id bersifat NOT NULL, jadi setiap post harus punya penulis. FK posts → authors memakai cascade, jadi menghapus penulis juga menghapus post-nya. Tabel penghubung post_tags memakai cascade di kedua sisi, jadi menghapus post atau tag akan merapikan baris penghubungnya secara otomatis.

Kebiasaan yang Menghindarkanmu dari Masalah di Kemudian Hari

• Atur PRAGMA foreign_keys = ON; di setiap koneksi. Jadikan bagian dari rutinitas aplikasimu saat membuka database, bukan sesuatu yang harus diingat-ingat.
• Tambahkan index pada kolom FK. SQLite otomatis membuat index untuk key milik induk, tapi tidak untuk milik anak, dan ON DELETE CASCADE melakukan pencarian pada tabel anak setiap kali induknya dihapus.
• Pilih ON DELETE dengan sengaja. Bawaannya (NO ACTION) aman, tapi berarti kamu akan menabrak "constraint failed" setiap kali mencoba bersih-bersih. Tentukan apa yang seharusnya terjadi lalu deklarasikan.
• Jalankan PRAGMA foreign_key_check; setelah migrasi atau impor massal untuk menangkap baris yatim piatu sebelum menjadi bug.

Berikutnya: INNER JOIN

Foreign key menggambarkan hubungannya; join adalah cara kamu benar-benar menjalankan query lintas hubungan itu. Halaman berikutnya membahas INNER JOIN: menggabungkan baris dari tabel-tabel yang saling terkait dan mendapatkan kolom yang kamu inginkan dari masing-masing tabel.