MySQL – Membuat Integritas Referensial


Integritas referensial merupakan cara untuk menjaga agar Kunci Asing (Foreign Key) suatu tabel dan Kunci Primer (Primary Key) milik tabel yang direferensi oleh Kunci Asing selalu konsisten. Untuk memperjelas hal ini, perhatikan hubungan antara tabel BUKU, JENIS, PENERBIT, dan PENULIS. (Keempat tabel ini telah saya tunjukkan pada tutorial-tutorial sebelumnya).

Apabila kita melihat deskripsi dari tabel BUKU dengan perintah SQL Berikut :

DESCRIBE buku;

+————-+————————–+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra
|
+————-+————————–+——+—–+———+—————-+
| no_buku | int(5) unsigned zerofill | NO | PRI | NULL | auto_increment
|
| isbn | char(15) | NO | | NULL |
|
| kd_jenis | char(4) | NO | MUL | NULL |
|
| judul | varchar(50) | NO | | NULL |
|
| kd_penulis | char(5) | NO | MUL | NULL |
|
| kd_penerbit | char(5) | NO | MUL | NULL |
|
| thn_terbit | char(4) | NO | | NULL |
|
| jml_buku | int(5) | YES | | NULL |
|
| rak_buku | varchar(10) | YES | | NULL |
|
| tgl_entry | date | YES | | NULL |
|
+————-+————————–+——+—–+———+—————-+

Tampak dengan jelas bahwa pada tabel BUKU terdapat 3 field (Kolom) yaitu, kd_jenis, kd_penulis, dan kd_penerbit yang sebenarnya ditujukan untuk mereferensi ke Kunci Primer milik tabel JENIS (kd_jenis), PENULIS (kd_penulis), PENERBIT (kd_penerbit). Agar hubungan antar Kunci Asing pada tabel BUKU dan Kunci Primer pada tabel JENIS, PENULIS, PENERBIT terjaga integritasnya, hubungan tersebut perlu menyertakan Integritas Referensial.

Dengan menggunakan Integritas Referensial akan ada jaminan bahwa nilai dalam suatu tabel yang merujuk ke tabel lain benar-benar nilai yang memang terdapat apda tabel yang dirujuk. Sebagai contoh, bila suatu nilai yang dimasukkanke dalam kd_jenis pada tabel BUKU tidak terdapat pada kd_jenis pada tabel JENIS, nilai tersebut akan ditolak.

Integritas Referensial antara tabel BUKU, JENIS, PENULIS, PENERBIT dapat dibentuk melalui perintah seperti berikut :

create table if not exists buku (

no_buku int(5) unsigned zerofill not null auto_increment,isbn char(15) not null,

kd_jenis char(4) not null,

judul varchar(50) not null,

kd_penulis char(5) not null,

kd_penerbit char(5) not null,

thn_terbit char(4) not null,

jml_buku int(5) not null,

rak_buku varchar(10) not null,

tgl_entry date not null,

foreign key (kd_jenis) references jenis(kd_jenis),

foreign key (kd_penulis) references penulis(kd_penulis),

foreign key (kd_penerbit) references penerbit(kd_penerbit),

primary key (no_buku)

);

Perhatikan bahwa kd_jenis, kd_penulis, kd_penerbit pada tabel BUKU berkedudukan sebagai kunci asing yang mereferensi ke kd_jenis yang terdapat pada tabel JENIS, kd_penulis yang terdapat pada tabel PENULIS, kd_penerbit yang terdapat pada PENERBIT, yang dinyatakan melalui:

foreign key (kd_jenis) references jenis(kd_jenis)

foreign key (kd_penulis) references penulis(kd_penulis)

foreign key (kd_penerbit) references penerbit(kd_penerbit)

Dengan cara seperti itu nilai yang diisikan pada kd_jenis, kd_penulis, kd_penerbit pada tabel BUKU harus berupa nilai yang terdapat pada kd_jenis, kd_penulis, kd_penerbit pada tabel JENIS, PENULIS, dan PENERBIT.

SQL juga menyediakan fitur yang dapat diatur pemakai sekiranya suatu baris pada tabel induk direferensi oleh suatu kunci asing dihapus atau diubah. Tindakan-tindakan yang dapat diatur oleh pemakai ini biasa disebut tindakan referensial. Penentuak tindakan ini berbentuk :

[ON UPDATE {RESTRICT | CASCADE | SET NULL}]

[ON DELETE {RESTRICT | CASCADE | SET NULL}]

Yang terdapat dalam tanda [] berarti opsional, sedangkan yang terletak dalam tanda {} berarti harus dipilih salah satu di antara pilihan-pilihan yang dipisahkan oleh tanda |.

Penjelasan :

  • UPDATE : menyatakan tindakan kalau pada tabel induk terjadi perubahan nilai.
  • DELETE : menyatakan tindakan kalau pada tabel induk terjadi penghapusan baris.

Adapun tindakan yang dapat didefinisikan pada tabel ON UPDATE maupun ON DELETE berserta penjelasannya sebagai berikut :

  • RESTRICT : menyatakan bahwa pengubahan atau penghapusan ditolak.
  • CASCADE : jika nilai kunci primer pada tabel induk berubah (UPDATE) maka kunci asing pada tabel yang mereferensi akan disesuaikan dengan nilai pada kunci primer tabel induk, sedangkan apabila terjadi proses DELETE semua kunci asing yang cocok dengan kunci primer pada tabel induk milik record yang dihapus akan ikut dihapus.
  • SET NULL : Menyatakan kunci asing akan diisi dengan NULL kalau kunci primer pada tabel induk yang nilainya sama dengan nilai pada kunci asing tersebut diubah atau dihapus.

Contoh berikut menunjukkan penggunaan tindakan referensial :

create table if not exists buku (

no_buku int(5) unsigned zerofill not null auto_increment,isbn char(15) not null,

kd_jenis char(4) not null,

judul varchar(50) not null,

kd_penulis char(5) not null,

kd_penerbit char(5) not null,

thn_terbit char(4) not null,

jml_buku int(5) not null,

rak_buku varchar(10) not null,

tgl_entry date not null,

foreign key (kd_jenis) references jenis(kd_jenis) on update cascade on delete restrict,

foreign key (kd_penulis) references penulis(kd_penulis) on update cascade on delete restrict,

foreign key (kd_penerbit) references penerbit(kd_penerbit) on update cascade on delete restrict,

primary key (no_buku)

);

Perintah diatas menyatakan bahwa :

  • Kalau nilai pada kunci primer dalam tabel JENIS, PENULIS, dan PENERBIT diubah, nilai pada kd_jenis, kd_penulis, kd_penerbit pada tabel BUKU ikut dibuah (Efek ON UPDATE CASCADE)
  • Kalau suatu record dalam tabel JENIS, PENULIS, dan PENERBIT dihapus, semua record pada tabel BUKU yang nilai pada kd_jenis, kd_penulis dan kd_penerbit nya sama dnegan nilai pada kd_jenis tabel JENIS, kd_penulis tabel PENULIS, kd_penerbit tabel PENERBIT yang dihapus akan ditolak (Efek ON UPDATE RESTRICT)