MySQL Referential Integrity

MySQL Memiliki sebuah feature dalam Relational Database yaitu Referential Integrity. 

Referential Integrity adalah sebuah cara untuk menjaga konsistensi data antara tabel yang saling ber-Relasi.

Untuk menggunakan feature Referential Integrity tipe dari Store Engine tabel yang digunakan adalah yang support Transaksional, salah satu contoh di MySQL yang support Transaksional adalah Store Engine INNODB.

Referential Integrity ada berberapa macam antara lain adalah: 

1. Restrict  bila dalam bahasa indonesia adalah membatasi, maksudnya adalah data pada table induk tidak bisa di delete atau di update bila data  tersebut memiliki relasi pada tabel lainnya.
2. Cascase bila dibahasa indonesiakan adalah bertingkat, maksudya adalah bila data pada table_induk di delete atau di update maka secara otomatis data pada tabel lain  yang memiliki relasi akan di delete/di update juga.

Sedangkan Kasus yang akan dipraktekan pada posting ini adalah tentang data prestasi karywan.

Design tabel pada kasus ini terdiri dari dua table yaitu tbl_karyawan untuk menyimpan data karyawan dan tbl_prestasi untu meyimpan data-data prestasi karyawan.

1. Studi Kasus Membuat Referential Integrity tipe Cascade. 

Membuat table tbl_karyawan untuk menyimpan data karyawan

CREATE TABLE `tbl_karyawan` (
  `kode` char(6) NOT NULL,
  `nama` varchar(255) NOT NULL,
  `tgl_lahir` date DEFAULT NULL,
  `alamat` tinytext,
  PRIMARY KEY (`kode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Perhatikan tulisan ENGINE=innoDB, itu adalah cara membuat tabel betipe Store Engine InnoDB.  Berikutnya adalah Memasukan sample data karyawan sebaknya dua buah

insert into tbl_karyawan values ('000001','Dendie Sanjaya','1985-01-26','Buah Batu'), ('000002','Yulianti','1990-10-30','Laswi');

Membuat tabel tbl_prestasi untuk menyimpan data prestasi

CREATE TABLE `tbl_prestasi` (
  `kode` char(6) NOT NULL,
  `kode_karyawan` char(6) NOT NULL,
  `prestasi` varchar(255) NOT NULL,
  `keterangan` text,
  PRIMARY KEY (`kode`),
  FOREIGN KEY (`kode_karyawan`) REFERENCES `tbl_karyawan` (`kode`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Perhatikan tulisan :

• FOREIGN KEY (`kode_karyawan`) REFERENCES `tbl_karyawan` (`kode`) itu adalah untuk membuat field kode_karyawan pada tbl_prestasi berelasi dengan field kode pada table karyawan 
• ON DELETE CASCADE ON UPDATE CASCADE adalah untuk membuat relasi tersebut bertipe CASCADE untuk event Delete data dan Update Data.   

Berikutnya adalah Memasukan sample data prestasi sebanyak tiga buah

insert into tbl_prestasi values ('P-0001','000001','Juara 1 Programming Tingkat Kota','-'), ('P-0002','000001','Juara 2 Olimpiade Matematika Tingkat Kota','-'), ('P-0003','000002','Runner Up  Debat Tingkat 2','-');

Bila kedua table tersebut dilihat akan tampil seperti dibawah ini

select * from tbl_karyawan;
+--------+----------------+------------+-----------+
| kode   | nama           | tgl_lahir  | alamat    |
+--------+----------------+------------+-----------+
| 000001 | Dendie Sanjaya | 1985-01-26 | Buah Batu | 
| 000002 | Yulianti       | 1990-10-30 | Laswi     | 
+--------+----------------+------------+-----------+

 select * from tbl_prestasi;
+--------+---------------+-------------------------------------------+------------+
| kode   | kode_karyawan | prestasi                                  | keterangan |
+--------+---------------+-------------------------------------------+------------+
| P-0001 | 000001        | Juara 1 Programming Tingkat Kota          | -          | 
| P-0002 | 000001        | Juara 2 Olimpiade Matematika Tingkat Kota | -          | 
| P-0003 | 000002        | Runner Up  Debat Tingkat 2                | -          | 
+--------+---------------+-------------------------------------------+------------+

Sekarang kita akan coba buktikan melakukan update data field kode dari tbl_karyawan, yang berkode 000001 menjadi 000003

update tbl_karyawan set kode = '000003' where kode = '000001';

Maka yang terjadi data pada field kode di tabel tbl_karyawan akan berubah dari kode 000001 menjadi 000003. Dan secara otomasi field kode_karyawan pada table tbl_prestasi akan  terubah juga dari kode 000001 menjadi 000003

select * from tbl_prestasi;
+--------+---------------+-------------------------------------------+------------+
| kode   | kode_karyawan | prestasi                                  | keterangan |
+--------+---------------+-------------------------------------------+------------+
| P-0001 | 000003        | Juara 1 Programming Tingkat Kota          | -          | 
| P-0002 | 000003        | Juara 2 Olimpiade Matematika Tingkat Kota | -          | 
| P-0003 | 000002        | Runner Up  Debat Tingkat 2                | -          | 
+--------+---------------+-------------------------------------------+------------+

Sekarang kita akan coba buktikan melakukan delete data field kode dari tbl_karyawan, yang berkode 000003

delete from tbl_karyawan where kode = '000003';

Maka yang terjadi data pada pada tabel tbl_karyawan kode 000003 telah di hapus dan secara otomasi field kode_karyawan pada table tbl_prestasi yang berkode 000003 dihapus juga.

select * from tbl_prestasi;
+--------+---------------+----------------------------+------------+
| kode   | kode_karyawan | prestasi                   | keterangan |
+--------+---------------+----------------------------+------------+
| P-0003 | 000002        | Runner Up  Debat Tingkat 2 | -          | 
+--------+---------------+----------------------------+------------+

2. Studi Kasus Membuat Referential Integrity tipe Restrict . 

Membuat table tbl_karywan untuk menyimpan data karyawan

CREATE TABLE `tbl_karyawan` (
  `kode` char(6) NOT NULL,
  `nama` varchar(255) NOT NULL,
  `tgl_lahir` date DEFAULT NULL,
  `alamat` tinytext,
  PRIMARY KEY (`kode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Perhatikan tulisan ENGINE=innoDB, itu adalah cara membuat tabel betipe Store Engine InnoDB. . Berikutnya adalah Memasuk sample data karyawan sebaknya dua buah.

insert into tbl_karyawan values ('000001','Dendie Sanjaya','1985-01-26','Buah Batu'), ('000002','Yulianti','1990-10-30','Laswi');

Membuat tabel tbl_prestasi untuk menyimpan data presetasi

CREATE TABLE `tbl_prestasi` (
  `kode` char(6) NOT NULL,
  `kode_karyawan` char(6) NOT NULL,
  `prestasi` varchar(255) NOT NULL,
  `keterangan` text,
  PRIMARY KEY (`kode`),
  FOREIGN KEY (`kode_karyawan`) REFERENCES `tbl_karyawan` (`kode`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

Perhatikan tulisan :

• FOREIGN KEY (`kode_karyawan`) REFERENCES `tbl_karyawan` (`kode`) itu adalah untuk membuat field kode_karyawan pada tbl_prestasi berelasi dengan field kode pada table karyawan 
• ON DELETE RESTRICT ON UPDATE RESTRICT adalah untuk membuat relasi tersebut bertipe Restrict untuk event Delete data dan Update Data.  

Berikutnya adalah Memasukan sample data prestasi sebanyak tiga buah

insert into tbl_prestasi values ('P-0001','000001','Juara 1 Programming Tingkat Kota','-'), ('P-0002','000001','Juara 2 Olimpiade Matematika Tingkat Kota','-'), ('P-0003','000002','Runner Up  Debat Tingkat 2','-');

Sekarang kita akan coba buktikan melakukan update data field kode dari tbl_karyawan, yang berkode 000001 menjadi 00003 

update tbl_karyawan set kode = '000003 ' where kode = '000001';

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`latihan`.`tbl_prestasi`, CONSTRAINT `tbl_prestasi_ibfk_1` FOREIGN KEY (`kode_karyawan`) REFERENCES `tbl_karyawan` (`kode`))

Maka yang terjadi adalah muncul error ERROR 1451 (23000) artinya adalah kode 00001 tidak dapat di update karena data 00001 digunakan pada pada tabel tbl_prestasi

Sekarang kita akan coba buktikan melakukan delete data field kode dari tbl_karyawan. yang kode 000001

delete from tbl_karyawan where kode = '000001';

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`latihan`.`tbl_prestasi`, CONSTRAINT `tbl_prestasi_ibfk_1` FOREIGN KEY (`kode_karyawan`) REFERENCES `tbl_karyawan` (`kode`))

Maka yang terjadi adalah muncul error ERROR 1451 (23000) juga artinya adalah kode 00001 tidak dapat di delete karena data 00001 digunakan pada pada tabel tbl_prestasi.

Agar data 00001 pada tbl_karyawan dapat di delete maka data 00001 pada field kode_karyawan di table tbl_prestasi harus dihapus lebih dahulu.