SQL ALTER TABLE Statement

We can change the structure of a table using the ALTER TABLE command. We can

• 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,

ALTER TABLE Customers
ADD phone varchar(10);

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

Add Multiple Columns in a Table

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

ALTER TABLE Customers
ADD phone varchar(10), age int;

Here, the SQL command adds the phone and age column in the Customers table.

Rename Column in a Table

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

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 Column in a Table

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.

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,

ALTER TABLE Customers
DROP COLUMN age;

Here, the SQL command removes the phone 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,

ALTER TABLE Customers
RENAME TO newCustomers;

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