Friday, February 26, 2010

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.

11 comments:

  1. makasih banyak infonya

    ReplyDelete
  2. mantap penjelasnnya mas , thanks for share :)

    ReplyDelete
  3. Sangat bermanfaat
    terima kasih :)

    ReplyDelete
  4. mas misal ada kasus seperti ini, saya memiliki tabel produk dengan field key kategori1,kategori2 dan tabel sub kategori dengan field key kategori1,kategori2 dimana jika terjadi update field kategori2 di tabel sub kategori akan merubah field kategori2 ditabel produk. dengan memfilter hanya data dengan field kategori1 yang sama....
    mohon pencerahannya mas dendi..... makasih!

    ReplyDelete
  5. dipraktikkan pake skrip php "mysql_query" kok error ya mas..?
    mohon pencerahannya..?

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete