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:

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

Table of Contents

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

 Introduction of MySQL Programs

 PHP Programs and MySQL Server

 Perl 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

 Window Functions for Statistical Analysis

 Use Index for Better Performance

 Transaction Management and Isolation Levels

 Locks Used in MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 System, User-Defined and Stored Procedure Variables

 MySQL Server Administration

 Storage Engines in MySQL Server

 InnoDB Storage Engine - Primary and Secondary Indexes

 Performance Tuning and Optimization

 Bulk Changes on Large Tables

 MySQL Server on macOS

 Installing MySQL Server on Linux

 Connection, Performance and Second Instance on Linux

 Archived Tutorials

 References

 Full Version in PDF/EPUB