Cara Merubah / Memodifikasi Tabel MySQL (Lengkap)

byBayu Radityo -Mei 27, 2021

0

Alter table pada mysql digunakan untuk merubah/memodifikasi struktur tabel, merubah struktur tabel disini dapat berupa menambah atau menghapus kolom, merubah tipe data, merubah nama tabel, merubah nama kolom, menambah atau menghapus constraint dsb. Adapun bentuk umum / format sintaks alter table adalah sebagai berikut:

mysql> help alter table;
Name: 'ALTER TABLE'
Description:
Syntax:
ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]
    [partition_options]

alter_option: {
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX | KEY} [index_name]
        [index_type] (key_part,...) [index_option] ...
  | ADD {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name]
        (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (key_part,...)
        [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
        [index_name] [index_type] (key_part,...)
        [index_option] ...
  | ADD [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (col_name,...)
        reference_definition
  | ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
  | DROP {CHECK | CONSTRAINT} symbol
  | ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED
  | ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}
  | ALTER [COLUMN] col_name
        {SET DEFAULT {literal | (expr)} | DROP DEFAULT}
  | ALTER INDEX index_name {VISIBLE | INVISIBLE}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST | AFTER col_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | {DISABLE | ENABLE} KEYS
  | {DISCARD | IMPORT} TABLESPACE
  | DROP [COLUMN] col_name
  | DROP {INDEX | KEY} index_name
  | DROP PRIMARY KEY
  | DROP FOREIGN KEY fk_symbol
  | FORCE
  | LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ORDER BY col_name [, col_name] ...
  | RENAME COLUMN old_col_name TO new_col_name
  | RENAME {INDEX | KEY} old_index_name TO new_index_name
  | RENAME [TO | AS] new_tbl_name
  | {WITHOUT | WITH} VALIDATION
}

partition_options:
    partition_option [partition_option] ...

partition_option: {
    ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | DISCARD PARTITION {partition_names | ALL} TABLESPACE
  | IMPORT PARTITION {partition_names | ALL} TABLESPACE
  | TRUNCATE PARTITION {partition_names | ALL}
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH | WITHOUT} VALI
DATION]
  | ANALYZE PARTITION {partition_names | ALL}
  | CHECK PARTITION {partition_names | ALL}
  | OPTIMIZE PARTITION {partition_names | ALL}
  | REBUILD PARTITION {partition_names | ALL}
  | REPAIR PARTITION {partition_names | ALL}
  | REMOVE PARTITIONING
}

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
}

table_options:
    table_option [[,] table_option] ...

table_option: {
    AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | ENGINE [=] engine_name
  | ENGINE_ATTRIBUTE [=] 'string'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT
}
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
  | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
  | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
  | STATS_SAMPLE_PAGES [=] value
  | TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
  | UNION [=] (tbl_name[,tbl_name]...)
}

Jika melihat format alter table secara keseluruhan tersebut memang agak membuat bingung karena terlihat sangat kompleks, namun jika dipelajari dengan baik maka kita dapat memodifikasi table sesuai dengan kebutuhan. Lalu, bagaimana cara menggunakan perintah alter table di MySQL? Sebagai contoh, perhatikan tabel berikut:

Cara Merubah Nama Tabel:

Dari tabel diatas penulis ingin mengganti/merubah nama tabel, format dan contohnya adalah sebagai berikut:

Format:

alter table nama_tabel rename to nama_tabel_baru;

Contoh:

mysql> Alter table disin rename to dosen;
Query OK, 0 rows affected (0.12 sec)

Cara Merubah Nama Kolom:

Lalu, dari tabel diatas penulis ingin mengganti nama kolom nami menjadi nama dengan format dan cara sebagai berikut:

Format:

alter table nama_tabel change nama_kolom_lama nama_kolom_baru tipe_data(length)
alter table nama_tabel rename column nama_kolom_lama to nama_kolom_baru;

Contoh:

mysql> alter table dosen change id_disin id_dosen int;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table dosen rename column nimi to nama;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Cara Menambah Kolom:

Format:

alter table nama_tabel add column (nama_kolom tipe_data(length));
alter table nama_tabel add column nama_tabel tipe_data(length) after nama_kolom;

Contoh:

mysql> alter table dosen add column (telepon char(12));
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table dosen add column telepon char(12) after kota;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

Jika saat menambahkan kolom tidak mendefinisikan klausa after maka kolom yang baru ditambahkan akan berada di posisi paling bawah, jika ingin menjadikannya di urutan pertama maka gunakan klausa first.

Cara Merubah Urutan Kolom:

Merubah urutan kolom hampir mirip dengan merubah tipe data / nama yakni bisa menggunakan change dan modify namun setelahnya kita perlu menambahkan klausa after nama_kolom atau first untuk menjadikannya kolom diurutan pertama.

Format:

alter table nama_tabel change nama_kolomN nama_kolomN tipe_data(length) after nama_kolom;
alter table nama_tabel modify nama_kolom tipe_data(length) after nama_kolom;

Contoh:

mysql> alter table dosen change telepon telepon char(12) after kota;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table dosen modify telepon char(12) after kota;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

Kita tidak dapat menggunakan perintah before, hanya after yang tersedia pada mysql.

Cara Merubah Tipe Data:

Format:

alter table nama_tabel change nama_kolom nama_kolom tipe_data_baru(length);
alter table nama_tabel modify nama_kolom tipe_data_baru(length);

Contoh:

mysql> alter table dosen change telepon telepon varchar(12);
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table dosen modify telepon varchar(12);
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

Cara Menambah Primary Key:

Penulis juga ingin menambahkan primary key dengan format dan cara sebagai berikut:

Format:

alter table nama_tabel add primary key (nama_kolom);
alter table nama_tabel add constraint nama_constraint primary key (nama_kolom);

Contoh:

mysql> alter table dosen add primary key (id_dosen);
Query OK, 0 rows affected (0.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table dosen add constraint pk_dosen primary key (id_dosen);
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

Jika kita ingin memberikan nama constraint dari primary key kita dapat menggunakan perintah add constraint nama_constraint.

Cara Menambah Foreign Key:

Format:

alter table nama_tabel add foreign key (nama_kolom) references nama_tabel_referensi(nama_kolom);
alter table nama_tabel add constraint nama_constraint foreign key (nama_kolom) references nama_tabel_referensi(nama_kolom);

Contoh:

mysql> alter table dosen add foreign key (dosen_id) references dosen (id_dosen);
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table dosen add constraint pk_dosen foreign key (dosen_id) references dosen (id_dosen);
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

Selain contoh singkat diatas masih ada banyak hal yang dapat diubah dari sebuah tabel, namun ada beberapa hal penting yang perlu diperhatikan ketika akan menggunakan perintah alter table:

Menambah dan Menghapus Kolom:

Gunakan ADD untuk menambah kolom pada tabel, dan DROP untuk menghapus kolom.
Untuk menambah kolom pada posisi/letak tertentu, gunakan First atau After nama_kolom. Jika tidak didefinisikan, secara default letak kolom yang baru ditambahkan akan berada di paling bawah.
Jika tabel hanya memiliki satu kolom, maka kolom tersebut tidak dapat dihapus selain dengan cara menghapus tabel (drop table).

Mengganti nama (Renaming), mendefinisikan ulang (Redefining) dan menyusun ulang kolom (Reordering):

Change

Dapat merubah nama kolom dan definisi kolom
Memiliki kapabilitas lebih tinggi dari modify atau rename column.
Dapat menggunakan klausa first atau after

Modify

Hanya dapat merubah definisi kolom dan tidak bisa merubah nama kolom
Lebih mudah digunakan daripada change untuk merubah definisi kolom tanpa mengganti nama kolom.
Dapat menggunakan klausa first atau after

Rename

Hanya dapat merubah nama kolom
Lebih mudah digunakan daripada change untuk merubah nama kolom

Primary Key dan Indexes:

Drop primary key digunakan untuk menghapus primary key, jika pada tabel tidak ada primary key maka akan menampilkan pesan error
Dapat menambah lebih dari satu primary key (composite key)
Tidak dapat menambahkan constraint unique ataupun primary key pada kolom yang sudah memiliki duplikasi data namun masih bisa menambahkan index saja
Mengganti nama index/constraint harus sama dengan nama index/constraint yang sudah ada, jika kita tidak tahu nama index/constraint yang digunakan maka bisa menggunakan perintah show create table nama_tabel untuk mencari tahu nama index/constraint
Jika ingin menghapus tabel sedangkan tabel berlaku sebagai parent table maka perlu menghapus foreign key yang terhubung ke parent table terlebih dahulu (child table)

Foreign Key dan Constraint:

Tidak dapat menambahkan constraint dengan nama yang sudah ada atau dalam kata lain nama constraint harus unique
Dapat menambahkan constraint foreign key pada kolom yang sudah memiliki duplikasi data, namun tidak bisa menambahkan constraint unique
Tidak dapat mengisi data yang data referensinya tidak ada pada parent table

Sampai disini kita telah membahas cara merubah / memodifikasi tabel mysql. Jika ada yang belum dipahami, jangan sungkan untuk bertanya melalui kolom komentar.

Cara Merubah / Memodifikasi Tabel MySQL (Lengkap)

Posting Komentar

Contact form