Sehebat apapun kita sebagai manusia, kita pasti pernah melakukan kecerobohan. Seorang developer yang sudah master pun terkadang tidak lepas dari yang namanya keteledoran. Ketika kita sudah merancang sistem yang kita anggap sudah sempurna, ternyata ada satu kesalahan kecil yang terlewat dari pandangan mata. Salah satu kasus yang dapat terjadi adalah, misalnya saja, ketika merancang basis data.
Sebagai contoh, kita mempunyai database bernama db_statistik, yang sudah di-deploy, yang terdiri dari tabel negara yang berisi data negara, alat_musik yang berisi data alat musik, dan alat_musik_favorit yang berisi data yang merepresentasikan hubungan antara negara dengan alat musik sehingga menghasilkan informasi berupa alat musik favorit dari setiap negara.
Rancangan tabel dari ilustrasi tersebut dapat dilihat pada Gambar 1 dan query definisi basis datanya dapat dilihat pada Kode 1.
--
-- Kode 1: Table structure for table `negara`
--
CREATE TABLE `negara` (
`negara_id` int(11) NOT NULL,
`nama` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Indexes for table `negara`
--
ALTER TABLE `negara`
ADD PRIMARY KEY (`negara_id`);
--
-- AUTO_INCREMENT for table `negara`
--
ALTER TABLE `negara`
MODIFY `negara_id` int(11) NOT NULL AUTO_INCREMENT;
-- --------------------------------------------------------
--
-- Table structure for table `alat_musik`
--
CREATE TABLE `alat_musik` (
`alat_musik_id` int(11) UNSIGNED NOT NULL,
`nama` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Indexes for table `alat_musik`
--
ALTER TABLE `alat_musik`
ADD PRIMARY KEY (`alat_musik_id`);
--
-- AUTO_INCREMENT for table `alat_musik`
--
ALTER TABLE `alat_musik`
MODIFY `alat_musik_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
-- --------------------------------------------------------
--
-- Table structure for table `alat_musik_favorit`
--
CREATE TABLE `alat_musik_favorit` (
`negara_id` int(11) NOT NULL,
`alat_musik_id` int(11) UNSIGNED NOT NULL,
`persentase_dominansi` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Indexes for table `alat_musik_favorit`
--
ALTER TABLE `alat_musik_favorit`
ADD PRIMARY KEY (`alat_musik_id`,`negara_id`),
ADD KEY `fk_alat_musik_favorit_alat_musik_id` (`alat_musik_id`) USING BTREE,
ADD KEY `fk_alat_musik_favorit_negara_id` (`negara_id`) USING BTREE;
--
-- Constraints for table `alat_musik_favorit`
--
ALTER TABLE `alat_musik_favorit`
ADD CONSTRAINT `fk_alat_musik_favorit_alat_musik_id`
FOREIGN KEY (`alat_musik_id`) REFERENCES `alat_musik` (`alat_musik_id`)
ON UPDATE RESTRICT ON DELETE RESTRICT,
ADD CONSTRAINT `fk_alat_musik_favorit_negara_id`
FOREIGN KEY (`negara_id`) REFERENCES `negara` (`negara_id`)
ON UPDATE RESTRICT ON DELETE RESTRICT;
Masalah
Pada kasus ini database tersebut sudah di-deploy. Tetapi setelah saya melakukan pemeriksaan ulang, ternyata ada suatu kesalahan yang telah saya buat, yaitu atribut kolom negara_id tidak unsigned, yang seharusnya saya atur menjadi demikian.
Pada Gambar 1, dapat dilihat bahwa setiap kolom primary key pada tabel alat_musik_favorit merupakan sebuah foreign key, yang memiliki relasi dengan tabel masing-masing yang memiliki nama kolom yang sama. Dengan relasi yang terbentuk itu, membuat kita tidak bisa memanipulasi kolom tersebut. Sedangkan masalahnya adalah kita ingin mengubah atribut foreign key tersebut secara langsung pada basis data yang sudah ter-deploy.
Solusi
Di MySQL, masalah ini dapat diatasi dengan query LOCK TABLES. Mekanismenya adalah dengan mengunci operasi write (tulis) pada tabel terlebih dahulu, dengan tujuan untuk menghindari resiko kerusakan integritas data. Pada website resmi MySQL, LOCK TABLES dijelaskan seperti berikut:
LOCK TABLES explicitly acquires table locks for the current client session.
A table lock only protects against inappropriate reads or writes by other sessions.
Setelah di-lock, kita lakukan alter tabel untuk menghapus index foreign key yang akan diubah atributnya, kemudian barulah kita bisa mengubahnya. Ketika selesai memanipulasi, jangan lupa untuk meng-unlock kembali tabelnya.
Untuk kasus pada tulisan ini, query yang melakukan operasi perubahan atribut dari kolom foreign key negara_id dapat dilihat pada Kode 2.
Peringatan! Jangan melakukan operasi ini di dalam environment production! Jika terpaksa, aktifkan mode maintenance terlebih dahulu pada website-mu agar user tidak dapat mengakses data yang ada.
--
-- Kode 2: Query untuk mengubah atribut kolom foreign key `negara_id`
--
/* Mengunci operasi write ke tabel terlebih dahulu, untuk menghindari resiko kerusakan integritas data */
LOCK TABLES
alat_musik_favorit WRITE,
negara WRITE;
/* Drop foreign key dari tabel `alat_musik_favorit` agar dapat memodifikasi kolom `negara_id` */
ALTER TABLE alat_musik_favorit
DROP FOREIGN KEY fk_alat_musik_favorit_negara_id,
MODIFY negara_id INT(11) UNSIGNED;
/* Jangan lupa modifikasi juga kolom yang sama pada tabel referensi */
ALTER TABLE negara
MODIFY negara_id INT(11) UNSIGNED AUTO_INCREMENT;
/* Setelah itu, atur kembali constraint dari foreign key tersebut */
ALTER TABLE alat_musik_favorit
ADD CONSTRAINT fk_alat_musik_favorit_negara_id FOREIGN KEY (negara_id) REFERENCES negara (negara_id);
/* Seluruh operasi sudah selesai dilakukan, sekarang unlock tabel yang sebelumnya di-lock */
UNLOCK TABLES;
Setelah kamu berhasil mengeksekusi query tersebut, kolom negara_id pada tabel turunan (alat_musik_favorit) ataupun tabel referensi (negara) akan memiliki atribut unsigned, dengan ilustrasi yang bisa kamu lihat pada Gambar 2.
Pada tulisan ini penjelasan yang saya sampaikan hanya untuk mengubah atribut kolom. Namun kamu juga bisa melakukan perubahan pada tipe data bahkan nama kolom foreign key.
Sekian tulisan saya mengenai cara mengubah tipe data dan atribut foreign key di MySQL. Jika kamu merasa terbantu dengan penjelasan yang ada pada tulisan ini, bagikanlah kepada temanmu yang merasa membutuhkannya juga. Tetapi jika kamu menemui masalah, silakan tinggalkan pertanyaan pada kolom komentar. Terima kasih!
