ALTER TABLE - Statement to Alter Table Structures

MySQL Tutorials - Herong's Tutorial Examples

∟ALTER TABLE - Statement to Alter Table Structures

A tutorial example is provided on how to use ALTER TABLE statements to add, delete, modify, columns and indexes.

An alter table statement allows you to change the structure of an existing table. It has a number of syntax formats:

1. To add a new column:

ALTER TABLE tbl_name ADD COLUMN col_name col_type col_options;

2. To add a new index:

ALTER TABLE tbl_name ADD INDEX idx_name (idx_column,...);

3. To drop an existing column:

ALTER TABLE tbl_name DROP COLUMN col_name;

4. To drop an existing index:

ALTER TABLE tbl_name DROP INDEX idx_name;
DROP INDEX idx_name on tbl_name;

5. To modify the type or options of an existing column:

ALTER TABLE tbl_name MODIFY COLUMN col_name col_type col_options;

6. To rename an existing column:

ALTER TABLE tbl_name RENAME COLUMN col_name TO new_col_name;

7. To reset the next value of the auto incremented column:

ALTER TABLE tbl_name AUTO_INCREMENT = value;

Note that in many cases, an alter table statement will cause the system to copy the original table into temporary copy, and perform the changes on the copy. If the table contains a huge amount of data, it will take a long time to execute an alter table statement.

Here is an example code of various alter table statements:

-- AlterTable.sql
-- Copyright (c) 1999 HerongYang.com. All Rights Reserved.
--
CREATE TABLE IF NOT EXISTS User (ID INT PRIMARY KEY AUTO_INCREMENT,
   Login CHAR(8) NOT NULL, Password CHAR(8) UNIQUE,
   Email CHAR(32) DEFAULT 'info@hy.com',
   INDEX LoginIndex (Login));
INSERT INTO User VALUES (null,'herong','8IS3KOXW','herong@hy.com');
INSERT INTO User (Login, Password, Email)
   VALUES ('mike','PZ0JG',null);
INSERT INTO User (Login, Password) VALUES ('mike','GZDN');
SELECT 'Show index #1:';
SHOW INDEX FROM User;
ALTER TABLE User ADD COLUMN Phone CHAR(10);
ALTER TABLE User DROP COLUMN Password;
ALTER TABLE User ADD INDEX EmailIndex (Email);
ALTER TABLE User DROP INDEX LoginIndex;
ALTER TABLE User AUTO_INCREMENT = 1000;
INSERT INTO User (Login, Phone) VALUES ('bill','8008008000');
SELECT 'Show data';
SELECT * FROM User;
SELECT 'Show index #2:';
SHOW INDEX FROM User;
DROP TABLE User;

Output:

Show index #1:
Show index #1:
Table   Non_unique   Key_name   Seq_in_index   Column_name ...
User    0            PRIMARY    1              ID
User    0            Password   1              Password
User    1            LoginIndex 1              Login

Show data
Show data
ID      Login   Email           Phone
1       herong  herong@hy.com  NULL
2       mike    NULL            NULL
3       mike    info@hy.com     NULL
1000    bill    info@hy.com     8008008000

Show index #2:
Show index #2:
Table   Non_unique   Key_name   Seq_in_index   Column_name ...
User    0            PRIMARY    1              ID
User    1            EmailIndex 1              Email

Note that:

Adding a column and dropping a column worked correctly.
Adding an index and dropping an index worked correctly too.
Changing the next value of the auto incremented column is easy.

Also note that the "ALTER TABLE" statement supports 4 ways to make changes to an existing column:

"ALTER TABLE tbl_name ALTER COLUMN col_name ..." - To change the column's default value only.
"ALTER TABLE tbl_name CHANGE COLUMN col_name ..." - To change column name and its properties.
"ALTER TABLE tbl_name MODIFY COLUMN col_name ..." - To modify column properties.
"ALTER TABLE tbl_name RENAME COLUMN col_name ..." - To change the column's name only.