ALTER TABLE - Merubah, Menambahkan, Menghapus Kolom Pada MariaDB dan MySQL
Untuk merubah table gunakan perintah ALTER TABLE diikuti dengan nama table yang ingin diubah spesifikasinya. Perintah ini dapat digunakan untuk menambahkan kolom, merubah definisi, merubah nama, modifikasi, dan menghapus kolom.
Pada tutorial yang akan Anda pelajari di bawah ini, Sebenarnya berlaku juga untuk ALTER TABLE pada MySQL. Jadi, meskipun Anda menggunakan database MySQL Anda tetap dapat belajar dan mengikuti panduan pada artikel ini dengan baik.
Syntax
ALTER [ONLINE] [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...
alter_specification:
table_option ...
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name ]
| ADD [COLUMN] (col_name column_definition,...)
| ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name]
[index_type] (index_col_name,...) [index_option] ...
| ADD FULLTEXT [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...
| ADD SPATIAL [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
reference_definition
| ALTER [COLUMN] col_name SET DEFAULT literal | (expression)
| ALTER [COLUMN] col_name DROP DEFAULT
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| DROP [COLUMN] [IF EXISTS] col_name [RESTRICT|CASCADE]
| DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
| DROP FOREIGN KEY fk_symbol
| DROP CONSTRAINT constraint_name
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name [, col_name] ...
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| ALGORITHM [=] {DEFAULT|INPLACE|COPY}
| LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
| FORCE
| partition_options
| ADD PARTITION (partition_definition)
| DROP PARTITION partition_names
| COALESCE PARTITION number
| REORGANIZE PARTITION [partition_names INTO (partition_definitions)]
| ANALYZE PARTITION partition_names
| CHECK PARTITION partition_names
| OPTIMIZE PARTITION partition_names
| REBUILD PARTITION partition_names
| REPAIR PARTITION partition_names
| EXCHANGE PARTITION partition_name WITH TABLE tbl_name
| REMOVE PARTITIONING
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value | (expression)]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string']
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
| data_type [GENERATED ALWAYS] AS ( <expression> ) {VIRTUAL | PERSISTENT}
[UNIQUE] [UNIQUE KEY] [COMMENT 'string']
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH | RTREE}
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
| CLUSTERING={YES| NO}
table_options:
table_option [[,] table_option] ... (see CREATE TABLE options)
Untuk sintaks versi lengkap dan kompleks lagi dapat dilihat pada web resmi (mariadb.com). Sumber link-nya sudah saya tulis di bawah halaman ini.
Sintaks Tambahan
Kita dapat menambahkan IF EXISTS dan IF NOT EXISTS pada definisi berikut:
ADD COLUMN [IF NOT EXISTS]
ADD INDEX [IF NOT EXISTS]
ADD FOREIGN KEY [IF NOT EXISTS]
ADD PARTITION [IF NOT EXISTS]
CREATE INDEX [IF NOT EXISTS]
DROP COLUMN [IF EXISTS]
DROP INDEX [IF EXISTS]
DROP FOREIGN KEY [IF EXISTS]
DROP PARTITION [IF EXISTS]
CHANGE COLUMN [IF EXISTS]
MODIFY COLUMN [IF EXISTS]
DROP INDEX [IF EXISTS]
IF EXISTS artinya, "Jalankan Perintah (definis) JIKA memang ADA", sedangkan IF NOT EXISTS yaitu kebalikannya, JIKA TIDAK ADA.
Definisi tersebut berlaku sejak MariaDB versi 10.0.2.
Penjelasan
ALTER TABLE .. merupakan perintah untuk merubah atau memodifikasi tabel yang sudah ada atau sebelumnya pernah kita buat, baik itu menambahkan kolom baru, modifikasi kolom ataupun menghapus kolom. Singkatnya, ALTER TABLE ini digunakan untuk merubah struktur sebuah table.
Untuk memudahkan pembelajaran, sekaligus mempraktekkan bagaimana merubah struktur tabel, Terlebih dahulu saya akan membuat sebuah table sebagai berikut:
CREATE TABLE `siswa` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nama_depan` varchar(10) NOT NULL,
`nama_belakang` varchar(15) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Saya anggap Anda sudah tahu bagaimana mengakses database MariaDB. dan menggunakan aplikasi client yang Anda sukai. Setelah membuat tabel tersebut, selanjutnya saya menjalankan perintah DESC siswa. Akan mendapatkan hasil sebagai berikut:
MariaDB [sekolah]> DESC siswa;
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| nama_depan | varchar(10) | NO | | NULL | |
| nama_belakang | varchar(15) | YES | | NULL | |
+---------------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
MariaDB [sekolah]>
Pada contoh tersebut, tabel siswa dibuat di database "sekolah". Jika belum tahu bagaimana membuat database sekolah, pelajari di Cara Membuat Database di MariaDB.
Harap diperhatikan dari contoh diatas. Untuk sementara kita baru memiliki 3 nama Field (kolom) yaitu id, nama_depan dan nama_belakang. Panduan selanjutnya, kita akan menambah, menghapus dan merubah kolom-kolom yang ada disini.
Langkah selanjutnya adalah memperaktekkan bagaimana merubah Table dengan perintah ALTER TABLE.
ADD COLUMN [IF NOT EXISTS]
ALTER TABLE tbl_name
ADD [COLUMN] [IF NOT EXISTS] col_name column_definition
[FIRST | AFTER col_name ]
Menambahkan Kolom Baru di dalam Tabel
ALTER TABLE siswa
ADD COLUMN IF NOT EXISTS
tanggal_lahir date;
Dari tabel sebelumnya (tabel "siswa"), Saya menjalankan kode diatas untuk menambahkan kolom tanggal_lahir. Tipe data yang saya gunakan adalah date (tanggal) karena hanya akan diisi dengan tanggal saja.
Hasilnya, adalah sebagai berikut:
MariaDB [sekolah]> ALTER TABLE siswa
-> ADD COLUMN IF NOT EXISTS
-> tanggal_lahir date;
Query OK, 0 rows affected (0.50 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [sekolah]>
Coba jalankan perintah DESC siswa;, maka akan terlihat tambahan kolom (Filed) baru yaitu tanggal_lahir seperti berikut ini:
MariaDB [sekolah]> DESC siswa;
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| nama_depan | varchar(10) | NO | | NULL | |
| nama_belakang | varchar(15) | YES | | NULL | |
| tanggal_lahir | date | YES | | NULL | |
+---------------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
Menambahkan Kolom Baru Setelah Kolom Lain
Bagaimana jika saya ingin menambahkan kolom tempat_lahir tapi diletakkan sesudah kolom nama_belakang? Solusinya, gunakan AFTER setelah definisi, berikut kodenya:
MariaDB [sekolah]> ALTER TABLE siswa
-> ADD COLUMN
-> tempat_lahir char(10)
-> AFTER nama_belakang;
Query OK, 0 rows affected (0.80 sec)
Records: 0 Duplicates: 0 Warnings: 0
Artinya, tambahkan kolom tempat_lahir setelah kolom nama_belakang.
Menambahkan Kolom Baru pada Index Pertama (FIRST)
MariaDB [sekolah]> ALTER TABLE siswa
-> ADD COLUMN
-> nis smallint(5)
-> FIRST;
Query OK, 0 rows affected (0.70 sec)
Records: 0 Duplicates: 0 Warnings: 0
Coba lihat hasilnya dengan menjalankan perintah DESCRIBE siswa;
DROP COLUMN [IF EXISTS]
ALTER TABLE tbl_name
DROP [COLUMN] [IF EXISTS] col_name [RESTRICT|CASCADE]
Menghapus Kolom dalam Sebuah Tabel
DROP COLUMN digunakan untuk menghapus kolom yang tidak kita inginkan. IF EXISTS digunakan untuk menghindari error terjadi. Jadi, hanya hapus kolom jika memang ada (if exists). Jika kolom yang ingin kita hapus ternyata tidak ada, maka hanya akan terdapat peringatan bukannya error.
Saya akan melanjutkan table siswa yang pernah dibuat sebelumnya. Anggap saja, saya tidak menginginkan kolom nis yang sebelumnya pernah dibuat dan ingin menghapusnya.
MariaDB [sekolah]> ALTER TABLE siswa
-> DROP COLUMN nis;
Query OK, 0 rows affected (0.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
MODIFY COLUMN
ALTER TABLE tbl_name
MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
Modifikasi Kolom, Ubah Tipe Data / Definisi Kolom
Melanjutkan tabel sebelumnya. Kali ini saya ingin merubah tipe data untuk kolom tempat_lahir yang sebelumnya memiliki tipe data char(10), misalkan saya ingin merubah ke tipe data varchar(15). Selain itu juga, saya pindahkan posisinya setelah kolom tanggal_lahir (terakhir). maka perintahnya adalah sebagai berikut:
MariaDB [sekolah]> ALTER TABLE siswa
-> MODIFY tempat_lahir varchar(15)
-> AFTER tanggal_lahir;
Query OK, 0 rows affected (1.37 sec)
Records: 0 Duplicates: 0 Warnings: 0
CHANGE COLUMN
ALTER TABLE tbl_name
CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
Merubah Kolom, Rename (Ganti Nama) dan Ubah Definisi
CHANGE COLUMN hampir sama dengan MODIFY COLUMN Hanya saja, dengan CHANGE COLUMN selain kita bisa merubah definisi kolom, kita juga dapat mengubah nama (rename) kolom. Posisi atau urutan (index) kolom tidak akan berubah setelah nama kolom diubah.
MariaDB [sekolah]> ALTER TABLE siswa
-> CHANGE tanggal_lahir tgl_lahir date NOT NULL;
Query OK, 0 rows affected (0.65 sec)
Records: 0 Duplicates: 0 Warnings: 0
Pada contoh diatas, saya merubah nama kolom dari tanggal_lahir menjadi tgl_lahir (supaya lebih pendek). Perlu diperhatikan penggantian nama kolom lama dengan kolom baru hanya dipisahkan dengan spasi. Setelah itu, kita sebutkan lagi tipe datanya. Pada contoh diatas menggunakan tipe data date dan tambahan definisi NOT NULL (tidak boleh kosong).
Coba lihat hasil akhirnya dengan menjalankan perintah DESC siswa;
Saya mendapatkan hasil akhir sebagai berikut:
MariaDB [sekolah]> desc siswa;
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| nama_depan | varchar(10) | NO | | NULL | |
| nama_belakang | varchar(15) | YES | | NULL | |
| tgl_lahir | date | NO | | NULL | |
| tempat_lahir | varchar(15) | YES | | NULL | |
+---------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
Jika hasil akhir Anda sama seperti diatas, berarti Anda mengikuti proses belajar dari awal sampai akhir dengan baik.
Catatan:
Pada kasus sebenarnya, nama kolom, tipe data dan definisi mungkin harus ditentukan dengan sebaik-baiknya sesuai kebutuhan. Contoh disini, mungkin tidak sesuai dan hanya dibuat untuk memudahkan proses pembelajaran semata.
SHARE