Foreign Key

sebuah tabel untuk dapat berelasi dengan tabel lainnya maka dibutuhkan foreign key. Untuk memudahkan dalam memahami pengertian foreign key kita akan melihat contoh tabel berikut ini. Di bawah ini ada dua tabel tabel yaitu users dan userdetails.

Tabel usersetails

ID_users

nama_depan

nama_belakang

Tempat_lahir

tanggal_lahir

Tabel users

ID_users

username

password

Pada dasarnya tabel ini dapat saja dibuat menjadi satu tabel tanpa harus melanggar aturan normalisasi. Namun, disini kita akan membuat dua tabel ini untuk membuat contoh dalam penggunaan foreign key. Untuk tabel userdetails maka SQL statement adalah

 CREATE TABLE IF NOT EXISTS userdetails( 

id_user int(3) NOT NULL,

nama_depan varchar(20) NOT NULL,

nama_belakang varchar(20) NOT NULL,

tempat_lahir varchar(25) NOT NULL,

tanggal_lahir date NOT NULL,

PRIMARY KEY (id_user)

) ENGINE=INNODB;

Sementara SQL statement untuk pembuatan tabel users adalah sebagai berikut

CREATE TABLE IF NOT EXISTS users( 

id_user int(3) NOT NULL,

username varchar(30) NOT NULL,

password varchar(255) NOT NULL,

UNIQUE KEY username(username),

FOREIGN KEY (id_user) REFERENCES userdetails (id_user)

) ENGINE=INNODB;

Dari definisi tabel di atas dapat kita lihat bahwa user_id pada tabel users merupakan sebuah foreign key yang mengacu pada kolom user_id pada tabel userdetail.

Aturan Insert Data

jika kita melakukan insert data ke dalam tabel user dengan id sembarang atau id yang tidak berada pada tabel yang menjadi acuan, maka errornya adalah sebagai berikut

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`pemrograman`.`users`, CONSTRAINT `users_ibfk_1` FOREIGN KEY (`id_user`) REFERENCES `userdetails` (`id_user`))

untuk lebih jelasnya dapat kita lihat pada screenshot berikut ini.

Pada kesimpulannya, untuk dapat memasukkan data ke tabel atau relasi yang mengacu ke sebuah tabel lain, atau tabel yang memiliki foreign key, maka tabel induk atau tabel yang dijadikan referensi harus memiliki data yang sama untuk kolom yang dijadikan foreign key pada tabel anak.

Misalkan saja untuk contoh di atas, maka kita harus terlebih dahulu memasukkan data ke dalam tabel userdetails dengan id_user=12, baru kemudian perintah SQL berikut dapat kita jalankan.


INSERT INTO users(id_user, username, password) VALUES(12, “slank”, md5(“virus”));

Untuk memasukkan data ke dalam tabel userdetails dapat menggunakan perintah SQL berikut


INSERT INTO userdetails(id_user, nama_depan, nama_belakang, tempat_lahir, tanggal_lahir) VALUES(12, “ade”, “irawan”, “miruk”, 1983-03-14);

Berikut adalah screenshot untuk perintah di atas ketika memasukkan data ke dalam tabel userdetails.

Kemudian kita akan mengulangi lagi memasukkan data ke dalam tabel users dengan id_user = 12, kali ini kita harapkan tidak akan terjadi error seperti sebelumnya. Berikut adalah screenshot dari insert data ke tabel users;

dari gambar di atas dapat kita lihat perintah yang sama yang sebelumnya error sekarang sudah tidak lagi. Maka kesimpulannya, untuk memasukkan data ke sebuah tabel anak, maka sebelumnya tabel induk harus diisi dengan data untuk kolom yang dijadikan foreign key dengan data yang sama. Untuk contoh di atas adalah id_user = 12 baik untuk tabel anak dan tabel induk.

Aturan update Data

Jika kita merubah primary key pada tabel induk yang dijadikan reference pada tabel anak maka akan terjadi error seperti pada screenshot berikut.

Yang sebenarnya ingin kita lakukan adalah melakukan perubahan id_user pada tabel userdetails dari 12 menjadi 13 dengan perintah


UPDATE userdetails SET id_user=13 WHERE id_user=12;

ada beberapa cara untuk dapat menangani masalah ini, tapi untuk sekarang bagian ini akan kita lewati.

Aturan delete Data

untuk menghapus data dari tabel induk yang memiliki data juga di tabel anak maka akan dihasilkan error yang sama. Seperti pada gambar screenshot berikut.

Untuk mengatasi masalah ini kita dapat merubah SQL untuk membuat tabel anak menjadi


CREATE TABLE IF NOT EXISTS users(

id_user int(3) NOT NULL,

username varchar(30) NOT NULL,

password varchar(255) NOT NULL,

UNIQUE KEY username(username),

FOREIGN KEY (id_user) REFERENCES userdetails (id_user)

ON DELETE CASCADE

) ENGINE=INNODB;

sebelumnya kita akan mencoba untuk menghapus tabel users untuk dapat menguji perintah SQL di atas untuk membuat tabel dengan perintah


DROP TABLE users;

kemudian baru kita buat tabel users yang baru dengan perintah di atas. Screenshot untuk perintah di atas adalah sebagai berikut

dari screenshot di atas dapat kita lihat bahwa kedua perintah di atas telah berhasil. Kemudian kita akan mencoba memasukkan data ke tabel anak dengan id_user=12 seperti sebelumnya lalu kita akan mencoba menghapus record di tabel induk yang memiliki id_user = 12. perintah SQL yang digunakan adalah sama seperti di atas.

Untuk menambah record ke tabel users menggunakan perintah berikut ini


INSERT INTO users(id_user, username, password) VALUES(12, “slank”, md5(“virus”));

untuk menghapus record pada tabel induk (userdetails) menggunakan perintah berikut


DELETE FROM userdetails WHERE id_user=12;

screenshot berikut adalah bukti bahwa dengan perintah yang baru untuk membuat tabel users maka error yang dihasilkan ketika menghapus record pada tabel induk dapat dihindari.

Hal yang sama juga dapat di terapkan pada update data pada tabel induk yaitu userdetail. Maka perintah untuk membuat tabel kita perbaiki kembali menjadi


CREATE TABLE IF NOT EXISTS users(

id_user int(3) NOT NULL,

username varchar(30) NOT NULL,

password varchar(255) NOT NULL,

UNIQUE KEY username(username),

FOREIGN KEY (id_user) REFERENCES userdetails (id_user)

ON UPDATE CASCADE ON DELETE CASCADE

) ENGINE=INNODB;

sama seperti sebelumnya sebelum menjalankan perintah ini kita harus menghapus tabel users terlebih dahulu. Selain menghapus tabel users sebenarnya kita masih bisa menggunakan perintah alter. Namun perintah alter akan kita pelajari pada bab lain.


DROP TABLE users;

kemudian kita membuat tabel users dengan perintah di atas. Selanjutnya adalah memasukkan record ke dalam tabel userdetails dengan perintah


INSERT INTO userdetails(id_user, nama_depan, nama_belakang, tempat_lahir, tanggal_lahir) VALUES(12, “ade”, “irawan”, “miruk”, 1983-03-14);

dilanjutkan dengan memasukkan data ke dalam tabel users menggunakan perintah yang juga sebelumnya sudah kita gunakan. Yaitu :

INSERT INTO users(id_user, username, password) VALUES(12, “slank”, md5(“virus”));

kemudian seperti pada subbab sebelumnya kita akan melakukan update pada tabel userdetail dengan perintah


UPDATE userdetails SET id_user=13 WHERE id_user=12;

Semua proses ini dapat kita lihat pada screenshot berikut.

Dari screenshot di atas dapat kita lihat bahwa proses update berjalan sesuai dengan yang kita inginkan.