SQL ALTER TABLE Statement

-- add phone column to Customers table
ALTER TABLE Customers
ADD phone varchar(10);

ALTER TABLE Syntax

The syntax of the SQL ALTER TABLE statement is:

ALTER TABLE table_name
clause supporting_codes;

Here,

• table_name is the name of the table to be modified
• clause gives further information on how the table is to be altered like ADD, RENAME COLUMN, etc.
• supporting_codes are the codes supporting the clause

ALTER TABLE Operations

We can perform the following operations on a table using the ALTER TABLE command:

• Add a column
• Rename a column
• Modify a column
• Delete a column
• Rename a table

Add Column in a Table

We can add columns in a table using the ALTER TABLE command with the ADD clause. For example,

-- add phone column to Customers table
ALTER TABLE Customers
ADD phone varchar(10);

Here, the SQL command adds a column named phone to the Customers table.

Add Multiple Columns in a Table

We can also add multiple columns at once to a table. For example,

-- add phone and age columns to Customers table
ALTER TABLE Customers
ADD phone varchar(10), age int;

Here, the SQL command adds the phone and age columns to the Customers table.

Note: Since our compiler uses SQLite, it does not support adding multiple columns with ALTER TABLE. However, many other database management systems support this command.

Rename Column in a Table

We can rename columns in a table using the ALTER TABLE command with the RENAME COLUMN clause. For example,

-- rename column customer_id to c_id
ALTER TABLE Customers
RENAME COLUMN customer_id TO c_id;

Here, the SQL command changes the column name of customer_id to c_id in the Customers table.

Modify the Data Type of a Column

We can also change the column's data type using the ALTER TABLE command with MODIFY or ALTER COLUMN clause. For example,

SQL Server

ALTER TABLE Customers
ALTER COLUMN age VARCHAR(2);

MySQL

ALTER TABLE Customers
MODIFY COLUMN age VARCHAR(2);

Oracle

ALTER TABLE Customers
MODIFY age VARCHAR(2);

PostgreSQL

ALTER TABLE Customers
ALTER COLUMN age TYPE VARCHAR(2);

Here, the SQL command changes the data type of the age column to VARCHAR in the Customers table.

Note: This command is not supported by SQLite.

Drop Column in a Table

We can also drop (remove) columns in a table using the ALTER TABLE command with the DROP clause. For example,

-- delete country column from Customers table
ALTER TABLE Customers
DROP COLUMN country;

Here, the SQL command removes the country column from the Customers table.

Rename a Table

We can change the name of a table using the ALTER TABLE command with the RENAME clause. For example,

-- rename Customers table to New_customers
ALTER TABLE Customers
RENAME TO New_customers;

Here, the SQL command renames the Customers table to New_customers.

Also Read:

• SQL CREATE TABLE