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);