KAL
Menu

Foreign Key

Rujukan antartabel dengan REFERENCES dan disiplin aksi seperti ON DELETE CASCADE.

Daftar isi

Foreign key adalah constraint yang menjalin kolom pada satu tabel ke kolom kunci pada tabel lain. Tabel yang memuat kolom rujukan disebut tabel anak (child table), sedangkan tabel yang dirujuk disebut tabel induk (parent table). Dengan foreign key, server menolak setiap nilai rujukan yang tidak menunjuk baris induk yang ada, sehingga relasi antartabel tetap benar tanpa bergantung pada kehati-hatian aplikasi. Topik ini membahas deklarasi foreign key, urutan pengisian tabel induk dan anak, dua error yang muncul ketika relasi dilanggar, serta aksi referensial seperti ON DELETE CASCADE.

Rujukan antartabel dalam skema perpustakaan#

Skenario perpustakaan memuat relasi yang wajar untuk foreign key. Tabel peminjaman berperan sebagai tabel anak yang menyimpan anggota_id dan buku_id, sedangkan tabel anggota dan buku berperan sebagai tabel induk yang menyimpan sumber kebenaran identitasnya. Berkas skema berikut membentuk ketiga tabel tersebut.

sql skema-perpustakaan.sql 
-- tidak-dijalankan

DROP DATABASE IF EXISTS perpustakaan;
CREATE DATABASE perpustakaan;
USE perpustakaan;

CREATE TABLE anggota (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  nama VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_email (email)
) ENGINE=InnoDB;

CREATE TABLE buku (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  kode_buku VARCHAR(12) NOT NULL,
  judul VARCHAR(200) NOT NULL,
  stok INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uq_kode (kode_buku)
) ENGINE=InnoDB;

CREATE TABLE peminjaman (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  anggota_id INT UNSIGNED NOT NULL,
  buku_id INT UNSIGNED NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'dipinjam',
  PRIMARY KEY (id),
  CONSTRAINT fk_peminjaman_anggota FOREIGN KEY (anggota_id)
    REFERENCES anggota (id) ON DELETE RESTRICT,
  CONSTRAINT fk_peminjaman_buku FOREIGN KEY (buku_id)
    REFERENCES buku (id) ON DELETE CASCADE
) ENGINE=InnoDB;

Dua rujukan pada tabel peminjaman sengaja memakai aksi penghapusan yang berbeda. Rujukan ke anggota memakai ON DELETE RESTRICT, artinya induk yang masih dirujuk tidak boleh dihapus, sedangkan rujukan ke buku memakai ON DELETE CASCADE, artinya penghapusan induk ikut menghapus baris anak yang merujuknya. Diagram berikut merangkum kedua relasi tersebut.

Relasi antartabel pada basis data perpustakaanpeminjamanidPKanggota_idFKbuku_idFKstatusanggotaidPKnamaemailbukuidPKkode_bukujudulstokON DELETE RESTRICTON DELETE CASCADE
Relasi antartabel pada basis data perpustakaan
bash 
# membuat ulang basis data perpustakaan dari nol
mysql -u root < skema-perpustakaan.sql

Mengisi induk sebelum anak#

Nilai pada kolom foreign key harus menunjuk baris induk yang sudah ada, sehingga pengisian data mengikuti urutan induk terlebih dahulu. Setelah anggota dan buku terisi, tabel peminjaman dapat merujuk kedua induknya dengan aman.

bash 
# mengisi dua tabel induk, lalu tabel anak yang merujuknya
mysql -t -u root perpustakaan <<'SQL'
INSERT INTO anggota (nama, email)
VALUES ('Ali', '[email protected]'), ('Budi', '[email protected]'), ('Citra', '[email protected]');
INSERT INTO buku (kode_buku, judul, stok)
VALUES ('AL-001', 'Algoritma Dasar', 2), ('ST-014', 'Struktur Data Terapan', 1);
INSERT INTO peminjaman (anggota_id, buku_id)
VALUES (1, 1), (2, 2);
SELECT id, anggota_id, buku_id, status FROM peminjaman ORDER BY id;
SQL
text Keluaran 
+----+------------+---------+----------+
| id | anggota_id | buku_id | status   |
+----+------------+---------+----------+
|  1 |          1 |       1 | dipinjam |
|  2 |          2 |       2 | dipinjam |
+----+------------+---------+----------+

Setiap baris peminjaman kini menunjuk pasangan anggota dan buku yang nyata. Status dipinjam terisi otomatis karena menjadi nilai bawaan kolom status, sehingga pernyataan penyisipan cukup menyebut kedua rujukannya.

Baris anak tanpa induk#

Error pertama muncul ketika tabel anak meminta rujukan yang tidak ada pada tabel induk. Percobaan berikut menyisipkan peminjaman untuk anggota bernomor 999, dan nomor tersebut tidak pernah ada pada tabel anggota.

bash 
# menyisipkan peminjaman dengan rujukan anggota yang tidak ada
mysql -u root perpustakaan -e "INSERT INTO peminjaman (anggota_id, buku_id) VALUES (999, 1)"
text Keluaran 
ERROR 1452 (23000) at line 1: Cannot add or update a child row: a foreign key constraint fails (`perpustakaan`.`peminjaman`, CONSTRAINT `fk_peminjaman_anggota` FOREIGN KEY (`anggota_id`) REFERENCES `anggota` (`id`) ON DELETE RESTRICT)

Error 1452 menunjukkan batasan kunci asing (constraint) yang dilanggar beserta definisinya, sehingga relasi yang bermasalah dapat langsung dikenali dari pesan kesalahan. Pelanggaran semacam ini umum terjadi ketika aplikasi menyimpan formulir menggunakan nilai ID yang sudah tidak berlaku, misalnya karena baris induk telah dihapus setelah formulir dibuka. Server menolak penyimpanan data tersebut sehingga tidak ada baris dengan referensi yang tidak valid yang perlu dibersihkan secara manual.

Menjaga sisi induk#

Error kedua muncul dari arah sebaliknya, yaitu ketika baris induk yang masih dirujuk berusaha dihapus. Anggota bernomor 1 memiliki peminjaman aktif, sehingga penghapusannya ditolak oleh fk_peminjaman_anggota.

bash 
# menghapus anggota yang masih memiliki peminjaman aktif
mysql -u root perpustakaan -e "DELETE FROM anggota WHERE id = 1"
text Keluaran 
ERROR 1451 (23000) at line 1: Cannot delete or update a parent row: a foreign key constraint fails (`perpustakaan`.`peminjaman`, CONSTRAINT `fk_peminjaman_anggota` FOREIGN KEY (`anggota_id`) REFERENCES `anggota` (`id`) ON DELETE RESTRICT)

Error 1451 menyatakan bahwa penghapusan induk gagal karena baris anak masih merujuknya. Perilaku ini persis maksud ON DELETE RESTRICT yang dinyatakan secara eksplisit pada skema, dan RESTRICT sebenarnya menjadi aksi bawaan bila aksi tidak dituliskan. Bandingkan dengan anggota bernomor 3 yang tidak pernah meminjam apa pun, yang penghapusannya berlangsung tanpa hambatan.

bash 
# menghapus anggota yang tidak memiliki peminjaman
mysql -u root perpustakaan -e "DELETE FROM anggota WHERE id = 3"

Aksi referensial: RESTRICT dan CASCADE#

Aksi referensial menentukan apa yang terjadi pada baris anak ketika baris induknya dihapus. Selain RESTRICT dan CASCADE, MySQL menyediakan SET NULL yang mengosongkan kolom rujukan pada baris anak, dengan syarat kolomnya mengizinkan NULL. Aksi serupa juga tersedia untuk pembaruan melalui ON UPDATE.

Pada skema topik ini, penghapusan buku dipilih memicu CASCADE karena baris peminjaman yang bukunya hilang tidak lagi bermakna. Jalankan penghapusan buku bernomor 1, lalu periksa keadaan tabel peminjaman.

bash 
# menghapus buku yang masih dirujuk, dengan aksi CASCADE
mysql -u root perpustakaan -e "DELETE FROM buku WHERE id = 1"
bash 
# memeriksa baris peminjaman yang tersisa
mysql -t -u root perpustakaan -e "SELECT id, anggota_id, buku_id, status FROM peminjaman ORDER BY id"
text Keluaran 
+----+------------+---------+----------+
| id | anggota_id | buku_id | status   |
+----+------------+---------+----------+
|  2 |          2 |       2 | dipinjam |
+----+------------+---------+----------+

Baris peminjaman yang merujuk buku bernomor 1 ikut terhapus secara otomatis, sedangkan peminjaman buku bernomor 2 tetap utuh. Inilah sifat CASCADE yang perlu diwaspadai, karena penghapusan satu baris induk dapat menghilangkan banyak baris anak sekaligus tanpa konfirmasi tambahan.

Definisi kanonis tabel peminjaman memperlihatkan kedua constraint foreign key yang sudah diberi nama, lengkap dengan aksi CASCADE yang tercatat.

bash 
# menampilkan definisi kanonis tabel peminjaman
mysql -t -u root perpustakaan -e "SHOW CREATE TABLE peminjaman\G"
text Keluaran 
*************************** 1. row ***************************
       Table: peminjaman
Create Table: CREATE TABLE `peminjaman` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `anggota_id` int unsigned NOT NULL,
  `buku_id` int unsigned NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'dipinjam',
  PRIMARY KEY (`id`),
  KEY `fk_peminjaman_anggota` (`anggota_id`),
  KEY `fk_peminjaman_buku` (`buku_id`),
  CONSTRAINT `fk_peminjaman_anggota` FOREIGN KEY (`anggota_id`) REFERENCES `anggota` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_peminjaman_buku` FOREIGN KEY (`buku_id`) REFERENCES `buku` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Kesalahan umum#

Kesalahan pertama adalah membuat kolom rujukan dengan tipe yang tidak kompatibel terhadap kolom induknya. Deklarasi semacam ini ditolak sejak awal, seperti pada percobaan berikut yang merujuk kolom id bertipe INT UNSIGNED menggunakan kolom bertipe BIGINT UNSIGNED.

bash 
# membuat tabel dengan tipe kolom rujukan yang tidak kompatibel
mysql -u root perpustakaan -e "CREATE TABLE uji_tipe (buku_id BIGINT UNSIGNED NOT NULL, FOREIGN KEY (buku_id) REFERENCES buku (id))"
text Keluaran 
ERROR 3780 (HY000) at line 1: Referencing column 'buku_id' and referenced column 'id' in foreign key constraint 'uji_tipe_ibfk_1' are incompatible.

Error 3780 melaporkan ketidakcocokan tersebut, dan perbaikannya adalah menyamakan tipe kedua kolom, termasuk atribut UNSIGNED-nya. Kesalahan kedua adalah mengisi tabel anak sebelum tabel induknya saat memuat data awal, yang selalu berakhir dengan error 1452. Urutkan berkas impor sehingga tabel induk terisi lebih dahulu, atau matikan pemeriksaan foreign key hanya selama pemuatan yang sudah diperiksa.

Kesalahan ketiga adalah menganggap bahwa relasi antartabel otomatis terjaga tanpa deklarasi foreign key. Tanpa deklarasi tersebut, server tidak mengetahui hubungan yang dimaksud antartabel, sehingga data dengan referensi yang tidak valid dapat terbentuk tanpa hambatan. Oleh karena itu, deklarasikan foreign key pada setiap relasi yang integritasnya harus dijaga. Pastikan pula kolom yang menjadi referensi memiliki tipe data yang sama persis dengan kolom kunci pada tabel induknya.

Ringkasan#

  • Foreign key menjalin tabel anak ke tabel induk melalui REFERENCES, dan server menolak rujukan yang tidak ada dengan error 1452.
  • Penghapusan induk yang masih dirujuk ditolak dengan error 1451 apabila aksinya RESTRICT, aksi bawaan foreign key InnoDB.
  • ON DELETE CASCADE menghapus baris anak bersama induknya, sedangkan SET NULL mengosongkan kolom rujukan yang mengizinkan NULL.
  • Tipe kolom rujukan harus kompatibel dengan kunci induk, dan ketidakcocokannya ditolak sejak deklarasi dengan error 3780.

Referensi#