Learn about SQL ALTER TABLE Statement with the help of examples. Learn how to modify the structure of an existing SQL table with the SQL ALTER TABLE statement.
In SQL, the ALTER TABLE
command is used to modify the structure of an existing table like adding, deleting, renaming columns, etc.
-- 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.
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 modifiedclause
gives further information on how the table is to be altered like ADD
, RENAME COLUMN
, etc.supporting_codes
are the codes supporting the clauseWe can perform the following operations on a table using the ALTER TABLE
command:
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.
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.
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.
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.
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.
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.
#sql