Tutorial innoDB MySQL

Dalam artikel ini akan kita bahas tentang innoDB dengan contoh kasus database mobilephonedb menggunakan dua tabel. Tabel pertama bernama merek untuk menampung nama merek atau perusahan dan tabel kedua bernama produk. Untuk mengikuti tutorial ini, anda boleh menggunakan MySQL client command line atau menggunakan PHPMyAdmin. Perhatikan diagram relasi tabelnya dibawah ini .

SQL script

SQL untuk membuat tabelnya adalah sebagai berikut. Tabel merek adalah tabel parent, sedangkan produk adalah tabel child.

-- -----------------------------------------------------
-- Table `mobilephonedb`.`merek`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mobilephonedb`.`merek` (
  `kd_merek` VARCHAR(10) NOT NULL ,
  `nama_merek` VARCHAR(45) NULL ,
  PRIMARY KEY (`kd_merek`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mobilephonedb`.`produk`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mobilephonedb`.`produk` (
  `idproduk` INT NOT NULL AUTO_INCREMENT ,
  `nama_produk` VARCHAR(45) NULL ,
  `kd_merek` VARCHAR(10) NULL ,
  PRIMARY KEY (`idproduk`) ,
  INDEX `kd_merek` (`kd_merek` ASC) ,
  CONSTRAINT `kd_merek`
    FOREIGN KEY (`kd_merek` )
    REFERENCES `mobilephonedb`.`merek` (`kd_merek` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

perhatikan kode saat membuat tabel produk pada bagian Constraint sampai akhir script, inilah perintah yang mengatur DRI pada tabel dengan Engine innoDB. MyISAM tidak punya fitur ini, makanya selalu gunakan innoDB sebagai engine Deault.
Hal yang paling ditekankan ditutorial ini adalah pada konsep Declarative Referential Integrity (DRI). DRI adalah fitur RDMS yang menekankan pada integritas data. Apa yang harus dilakukan jika primary key ditabel parent diganti? apa efeknya bagi child tabel? dalam contoh kita, misal kita punya merek APPLE, apa yang terjadi pada tabel produk jika kd_merek Apple ditabel merek kita ganti menjadi APPL? Apakah dibiarkan begitu saja, atau otomatis terganti atau diset NULL?

OK, sebagian dari kamu pasti bingung, itulah mengapa kita sebaiknya menggunakan contoh untuk menjelaskan konsep DRI. Sekarang, silahkan insertkan data data ini kedalam tabel Merek dan tabel produk.

INSERT INTO `merek` (`kd_merek`, `nama_merek`) VALUES
('APPL', 'Apple Computer'),
('SMSNG', 'Samsung Electronik');

INSERT INTO `produk` (`idproduk`, `nama_produk`, `kd_merek`) VALUES
(1, 'iPhone 5', 'APPL'),
(2, 'iPhone 4S', 'APPL'),
(3, 'Galaxy Nexus', 'SMSNG'),
(4, 'Galaxy SIII', 'SMSNG');

Efek DRI pada insert data

mysql> select * from merek;
+----------+--------------------+
| kd_merek | nama_merek         |
+----------+--------------------+
| APPL     | Apple Computer     |
| SMSNG    | Samsung Electronik |
+----------+--------------------+
2 rows in set (0.00 sec)

mysql> select * from produk;
+----------+--------------+----------+
| idproduk | nama_produk  | kd_merek |
+----------+--------------+----------+
|        1 | iPhone 5     | APPL     |
|        2 | iPhone 4S    | APPL     |
|        3 | Galaxy Nexus | SMSNG    |
|        4 | Galaxy SIII  | SMSNG    |
+----------+--------------+----------+
4 rows in set (0.00 sec)

mysql> insert into produk(nama_produk,kd_merek)
    -> values('Experia','sony');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`mobilephonedb`.`produk`, CONSTRAINT `kd_merek` FOREIGN KEY (`kd_merek`) R
EFERENCES `merek` (`kd_merek`) ON DELETE CASCADE ON UPDATE CASCADE)

Insert gagal dilakukan karena merek sony tidak ada ditabel merek. inilah salah satu keunggulan InnoDB. kita tidak mungkin menambahkan data di tabel child jika tabel parentnya tidak mempunyai data yang dirujuk. Jika anda ingin menambahkan data diatas, anda harus memasukan data merek sony terlebih dahulu di tabel merek. perhatikan script dibawah ini.

mysql> insert into merek values('sony','SONY Mobile');
Query OK, 1 row affected (0.08 sec)

mysql> insert into produk(nama_produk,kd_merek)
    -> values('Experia','sony');
Query OK, 1 row affected (0.19 sec)

sekarang kita bisa menambah produk experia karena sony sudah ada ditabel merek.

Efek DRI pada update data

pada saat membuat tabel produk, kita mempunyai aturan, on Update cascade, artinya saat isi tabel merek diupdate, maka otomatis tabel produk yang merujuk ke data tersebut juga ikut terupdate.

mysql> select * from merek;
+----------+--------------------+
| kd_merek | nama_merek         |
+----------+--------------------+
| APPL     | Apple Computer     |
| SMSNG    | Samsung Electronik |
| sony     | SONY Mobile        |
+----------+--------------------+
3 rows in set (0.00 sec)

mysql> select * from produk;
+----------+--------------+----------+
| idproduk | nama_produk  | kd_merek |
+----------+--------------+----------+
|        1 | iPhone 5     | APPL     |
|        2 | iPhone 4S    | APPL     |
|        3 | Galaxy Nexus | SMSNG    |
|        4 | Galaxy SIII  | SMSNG    |
|        6 | Experia      | sony     |
+----------+--------------+----------+
5 rows in set (0.00 sec)

mysql> update merek set kd_merek='SAMSUNG' where kd_merek='SMSNG';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from produk;
+----------+--------------+----------+
| idproduk | nama_produk  | kd_merek |
+----------+--------------+----------+
|        1 | iPhone 5     | APPL     |
|        2 | iPhone 4S    | APPL     |
|        3 | Galaxy Nexus | SAMSUNG  |
|        4 | Galaxy SIII  | SAMSUNG  |
|        6 | Experia      | sony     |
+----------+--------------+----------+
5 rows in set (0.00 sec)

perhatikan query diatas, ketika kita mengupdate kd_merek SMSNG menjadi SAMSUNG ditabel merek, secara otomatis data kd_merek di tabel produk juga ikut berubah, keren kan?

Efek DRI pada delete data

karena tabel produk diset on delete cascade maka secara otomatis jika ada data di tabel merek dihapus, maka secara otomatis, semua data di tabel produk yang merujuk ke merek tersebut juga ikut terhapus. Sebagai contoh, ketika kita menghapus merek APPL, maka secara otomatis data produk dengan kd_merek apple di tabel produk secara otomatis ikut terhapus.

mysql> select * from merek;
+----------+--------------------+
| kd_merek | nama_merek         |
+----------+--------------------+
| APPL     | Apple Computer     |
| SAMSUNG  | Samsung Electronik |
| sony     | SONY Mobile        |
+----------+--------------------+
3 rows in set (0.00 sec)

mysql> select * from produk;
+----------+--------------+----------+
| idproduk | nama_produk  | kd_merek |
+----------+--------------+----------+
|        1 | iPhone 5     | APPL     |
|        2 | iPhone 4S    | APPL     |
|        3 | Galaxy Nexus | SAMSUNG  |
|        4 | Galaxy SIII  | SAMSUNG  |
|        6 | Experia      | sony     |
+----------+--------------+----------+
5 rows in set (0.00 sec)

mysql> delete from merek where kd_merek='APPL';
Query OK, 1 row affected (0.08 sec)

mysql> select * from produk;
+----------+--------------+----------+
| idproduk | nama_produk  | kd_merek |
+----------+--------------+----------+
|        3 | Galaxy Nexus | SAMSUNG  |
|        4 | Galaxy SIII  | SAMSUNG  |
|        6 | Experia      | sony     |
+----------+--------------+----------+
3 rows in set (0.00 sec)

Sekarang, tugas administrator dan programmer lebih mudah bukan? Programmer tidak perlu melakuan operasi update dan delete secara manual di tabel child jika tabel parentnya diupdate atau didelete.

Operasi yang lain?

Selain cascade, ada 3 aturan lagi dalam masalah DRI di innoDB yaitu
DO NOTHING
saat data ditabel parent diubah, tabel di child tidak ada perubahan (tidak berefek)
SET NULL
saat data ditabel parent di ubah, tabel di child di set ke NULL untuk kolom foreign key yang merujuk ke tabel parent.

RESTRICTED
Aturan ini memaksa data tabel parent tidak boleh diubah atau dihapus selama masih ada data ditabel child yang merujuknya.

Sekian dulu, semoga bermanfaat!