Cara menggunakan kunci mul di mysql

Dalam database relasional, ada istilah primary key dan foreign key. Tujuan utama dari kedua istilah tersebut adalah identifikasi setiap tabel dan kejelasan hubungan antara 2 tabel atau lebih. Kunci utama adalah satu atau beberapa kolom dalam tabel yang mengidentifikasi setiap kolom dan baris dalam tabel. Sedangkan Foreign Key adalah satu atau beberapa kolom pada tabel yang merupakan primary key pada tabel satu tetapi diletakkan pada tabel yang mana tabel tersebut berhubungan dengan tabel itu sendiri.

Pada gambar di atas, nim on table mhs adalah primary key. Sedangkan primary key pada tabel matkul adalah id_matkul. Kemudian id_matkul dilampirkan pada tabel mhs yang kita sebut sebagai foreign key

Saya biasanya merujuk ke tabel yang berisi kunci asing sebagai tabel anak karena tabel tersebut terkait dengan tabel lain. Sedangkan tabel terkait saya sebut sebagai tabel induk. Pada contoh tabel di atas, mhs adalah tabel anak sedangkan matkul adalah tabel induk
Contoh isi kedua tabel tersebut dapat dilihat di bawah ini

Tabel hal
Nama Nim: Alamat: Id_matkul
10107644 Adiputra Artupida Bekasi IF123
10107634 Hermansyah Bandung IF222
10107635 Budianto Nugroho Semarang IF111
10107636 Silan.Bekasi IF333

Meja Matkul
Id_matkul        Nm_matkul                   Total_sks
IF123                 Pemrograman Web         3
IF111                  Pemrograman C            3
IF222 Pemrograman Java3
Pemrograman OOP IF3333

Saya langsung praktek di mysql. Ini kodenya

CREATE TABLE IF NOT EXISTS matkul(
id_matkul CHAR(5) NOT NULL PRIMARY KEY,
nm_matkul VARCHAR(30) NOT NULL,
jml_sks INT(3) NOT NULL
);

 

BUAT TABEL JIKA TIDAK ADA mhs(
nim CHAR(8) BUKAN KUNCI UTAMA NULL,
nama VARCHAR(50) BUKAN NULL,
alamat VARCHAR(60) NOT NULL,
id_matkul CHAR(5),
REFERENSI KUNCI ASING(id_matkul) matkul(id_matkul)
);

MASUKKAN KE NILAI mhs
('10107633','Adiputra Artupida','Bekasi','IF123'),
('10107634', 'Hermansyah', 'Bandung', 'IF222'),
('10107635', 'Budianto Nugroho', 'Semarang', 'IF111'),
('10107636','Silan','Bekasi','IF333');

MASUKKAN KE NILAI matkul
('IF123', 'Pemrograman Web',3),
('IF111', 'Pemrograman C',3),
('IF222', 'Pemrograman Java',3),
('IF333','Pemrograman OOP',3);

Lihatlah baris kode pada tabel mhs,

FOREIGN KEY(id_matkul) REFERENCES matkul(id_matkul)_

Itulah cara mendefinisikan kunci asing di mysql. Id_matkul didefinisikan sebagai kunci asing yang mengacu pada tabel matkul di kolom id_matkul

Yang ingin saya fokuskan pada artikel ini adalah apa yang terjadi jika data matkul misalnya data dengan id_matkul='IF123' dihapus?

DELETE FROM matkul WHERE id_matkul=’IF123’

Maka pasti ada kesalahan

"Tidak dapat menghapus atau memperbarui baris induk. batasan kunci asing gagal ('test'. ’mhs’, CONSTRAINT ‘mhs_ibfk_3’ KUNCI ASING (‘id_matkul’) REFERENSI ‘matkul’ (‘id_matkul))

Ini berarti tidak mungkin menghapus atau memperbarui kolom di tabel induk karena direferensikan di tabel mhs
Lalu apa solusinya?

Di MYSQL, kita harus menambahkan perintah ON DELETE [opsi] dan ON UPDATE [opsi] ke tabel yang mereferensikan kunci asing. Opsi pada perintah dijelaskan di bawah ini

1. RESTRICT, Jika tabel anak berisi nilai di kolom terkait yang nilainya sama dengan kolom terkait di tabel induk, baris di tabel induk tidak dapat dihapus, dan nilai di kolom terkait tidak dapat diperbarui. Ini adalah opsi default jika klausa ON DELETE atau ON UPDATE tidak ditentukan

2. CASCADE, Baris-baris pada tabel anak yang berisi nilai-nilai yang juga terdapat pada kolom terkait pada tabel induk dihapus ketika baris terkait dihapus dari tabel induk. Baris di tabel anak yang berisi nilai yang juga ditemukan di kolom terkait dari tabel induk diperbarui saat nilai terkait diperbarui di tabel induk

3. SET NULL, Nilai di kolom terkait dari tabel anak disetel ke NULL saat baris dengan data terkait di tabel induk dihapus dari tabel induk atau saat data terkait di tabel induk diperbarui. Untuk menggunakan opsi ini, semua kolom terkait di tabel anak harus mengizinkan nilai NULL

4. TIDAK ADA TINDAKAN Tidak ada tindakan yang diambil di tabel anak saat baris dihapus dari tabel induk atau nilai di kolom terkait di tabel induk diperbarui

5. SET DEFAULT Nilai dalam kolom terkait dari tabel anak disetel ke nilai defaultnya ketika baris dihapus dari tabel induk atau kolom terkait dari tabel induk diperbarui.

Saat kita mendefinisikan kunci asing dengan
REFERENSI KUNCI ASING(id_matkul) matkul(id_matkul)
Jadi perintah ON DELETE dan ON UPDATE memiliki opsi RESTRICT karena defaultnya dari referensi foreign key yang artinya tidak bisa dihapus atau diupdate. Hal itulah yang menyebabkan error pada saat kita menghapus atau mengupdate tabel induk

Coba sekarang ubah struktur tabel dari mhs dengan mengubah referensi kunci asing dengan opsi ON DELETE dan ON UPDATE CASCADE

Karena kita menggunakan OPSI CASCADE pada ON DELETE dan ON UPDATE, ketika kita menghapus salah satu kolom pada tabel induk, tabel anak juga akan dihapus. Terlihat bahwa data siswa bernama Adiputra Artupida dihapus karena Adiputra Artupida mengambil matkul dengan id 'IF123' dimana id tersebut dihapus dari tabel matkul

Selebihnya silahkan perbaiki diri. Saran jika kita ingin menyelesaikan foreign key biasakan menggunakan perintah ON UPDATE dan ON DELETE dengan OPTIONS disesuaikan dengan kebutuhan

Langkah-langkah untuk membuat kunci asing?

Bisakah 2 kunci utama dalam 1 tabel?

Apa itu kunci utama di MySQL?

Kapan Anda bisa menggunakan kunci asing?