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;

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 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) 2015, 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.comm');
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.comm  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:

Last update: 2015.

Table of Contents

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

 Introduction of MySQL Programs

 Perl Programs and MySQL Servers

 PHP Programs and MySQL Servers

 Java Programs and MySQL Servers

 Datatypes and Data Literals

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

 Table Column Types for Different Types of Values

Using DDL to Create Tables and Indexes

 CREATE TABLE - Statement to Create Tables

 Column Options When Creating Tables

 CREATE INDEX - Statement to Create Indexes

ALTER TABLE - Statement to Alter Table Structures

 Using DML to Insert, Update and Delete Records

 Using SELECT to Query Database

 Transaction Management and Isolation Levels

 Locks Used in MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 Outdated Tutorials

 References

 PDF Printing Version