Introduction

ALTER statement is used for altering the table structure. It can be used for

  • Add a column to existing table
  • Rename any existing column
  • Change datatype of any column or to modify its size
  • Drop a column from the table

Add New Column

To add new column to existing table

ALTER TABLE table_name
ADD column_name data_type column_constraint;

If you want to add multiple columns to a table at once using a single ALTER statement, use the following syntax:

ALTER TABLE table_name
ADD 
    column_name_1 data_type_1 column_constraint_1,
    column_name_2 data_type_2 column_constraint_2,
    ...,

Modify Existing Column

Syntax to modify the data type of a column is

ALTER TABLE table_name 
ALTER COLUMN column_name new_data_type(size);

Following changes to an existing column of a table are allowed

  • Modify data type
  • Change size
  • Add a NOT NULL constraint

Drop Column

To remove unused column from a table use the ALTER TABLE DROP COLUMN statement as follows:

ALTER TABLE table_name
DROP COLUMN column_name;

Column that has a PRIMARY KEY or a FOREIGN KEY constraint cannot be deleted.

Example

/* Create a Person table */
CREATE TABLE Person (
  id INT PRIMARY KEY,
  first_name VARCHAR (50) NOT NULL,
  last_name VARCHAR (50) NOT NULL,
  phone VARCHAR(20) DEFAULT 'None'
);

/* Drop last_name column in the table */
ALTER TABLE person DROP COLUMN last_name

/* Add last_name column in the table */
ALTER TABLE person ADD COLUMN last_name VARCHAR (50)

/* Increase the size of last_name column in the table */
ALTER TABLE person MODIFY last_name VARCHAR(100);

Reference

SQL ALTER TABLE Statement