SQL Alter Table

In Structured Query Language, if you want to add columns in an existing table, then modify the table, or delete columns from the table. All these operations are allowed only while using the ALTER TABLE statement. The ALTER TABLE statement also permits users to add or drop the SQL constraints on the existing tables in the SQL.

The ALTER TABLE statement also permits the user to rename the existing table.

ALTER TABLE ADD COLUMN statement in SQL

This is used when you need to add columns to the existing table. In such situation, rather than creating a new table, you can add a column in an existing table using ADD keyword.

Syntax of ALTER TABLE ADD COLUMN statement in SQL

ALTER TABLE Table_Name ADD Column_Name Data_Type;   

The above syntax is used to add a column in an existing table, and allows the users to add only a single field in the table.

To add more than one column in an existing table, use the following syntax:

ALTER TABLE Table_Name ADD (Column_Name1 Data_Type, Column_Name2 Data_Type);

Let's understand the ALTER TABEL ADD COLUMN in SQL with examples.

Example of ALTER TABLE ADD COLUMN in SQL

We have taken multiple SQL examples, which will help you understand adding a single column and multiple columns in the existing table using ALTER TABLE ADD COLUMN statement.

Consider the following tables along with the given records.

Table 1: Mobile_Plan:

Package_Id	Data_Id	Talktime_Id
1	2	1
2	3	2
3	1	3

Table 2: Data_Plan:

Data_Id	Data_Limit	Data_Price
1	2	30
2	5	70
3	4	55

Table 3: Talktime_Plan:

Talktime_Id	Talktime _Limit	Talktime _Price
1	100	150
2	70	105
3	60	90

Table 4: Customer (Empty Table)

Example 1: Write a query to add a new column Extra_Data_Price in the Data_Plan Table.

ALTER TABLE Data_Plan ADD Extra_Data_Price int; 

We have used the ALTER TABLE ADD statement in the above query to add a new Extra_Data_Price column in the Data_Plan table. We have added a single column in the existing table.

We will now use the DESC keyword and the table name to cross-check whether the Extra_Data_Price column was added or not successfully in the Data_Plan table.

DESC Data_Plan;   

Field	Type	Null	Key	Default	Extra
Data_Id	int(11)	NO	PRI	NULL
Data_Limit	int(11)	YES		NULL
Data_Price	int(11)	YES		NULL
Extra_Data_Price	Int(11)	YES

Example 2: Write a query to add multiple columns CustomerId, Customer_Name, and Phone_Number in the Customer table, which is empty, having no columns.

ALTER TABLE Customer ADD (CustomerId int, Customer_Name varchar(20), Phone_Number varchar(10));

We have used the ALTER TABLE ADD statement in the above query to add multiple new columns CustomerId, Customer_Name, and Phone_Number in the Customer table. We have added multiple columns in the existing table.

We will now use the DESC keyword and the table name to cross-check whether the CustomerId, Customer_Name, and Phone_Number columns were added successfully or not in the Customer table.

DESC Customer;  

Field	Type	Null	Key	Default	Extra
CustomerId	int(11)	YES		NULL
Customer_Name	varchar(20)	YES		NULL
Phone_Number	varchar(10)	YES		NULL

Suppose you want to add constraints like Primary Key, Foreign Key to an existing table. We will take an example of adding Primary Key constraints and Foreign Key constraints to the existing table. We can also add those constraints using ALTER TABLE ADD statement.

Example 3: Write a query to add Primary Key constraint on the column name CustomerId in the Customer Table.

ALTER TABLE Customer ADD Primary Key(CustomerId);

In the above query, we have used ALTER TABLE ADD statement to add the Primary Key constraint on the column name CustomerId in the Customer Table. We have used the Primary Key keyword with ALTER TABLE ADD statement to add a constraint on the CustomerId.

We will now use the DESC keyword and the table name to cross-check whether the CustomerId column key is changed to PRI in the Customer table.

DESC Customer;  

Field	Type	Null	Key	Default	Extra
CustomerId	int(11)	YES	PRI	NULL
Customer_Name	varchar(20)	YES		NULL
Phone_Number	varchar(10)	YES		NULL

Example 4: Write a query to add Foreign Key constraint on the column name Package_Id in the Customer Table.

ALTER TABLE Customer ADD (Package_Id int, FOREIGN KEY(Package_Id) references Mobile_Plan(Package_ID));

In the above query, we have first added the Package_Id column in the Customer table, and then we have used Foreign Key on the same column Package_Id in the same query using ALTER TABLE ADD statement.

We will now use the DESC keyword and the table name to cross-check whether the Package_Id column was added or not successful in the Customer table and key change to MUL.

DESC Customer;   

Field	Type	Null	Key	Default	Extra
CustomerId	int(11)	YES	PRI	NULL
Customer_Name	varchar(20)	YES		NULL
Phone_Number	varchar(10)	YES		NULL
Package_Id	Int(11)	YES	MUL	NULL

ALTER TABLE MODIFY COLUMN statement in SQL

Suppose you want to update the column name or definition like a data type. In that case, we will use ALTER TABLE MODIFY statement to update the column data type of the already existing table. We will use MODIFY keyword just after the Table_Name in the statement.

Syntax of ALTER TABLE MODIFY COLUMN statement in SQL

ALTER TABLE Table_Name MODIFY Column_Name Data_Type;   

The above syntax is used to modify columns in an existing table allows the user to modify only a single field.

To modify more than one column in an existing table-use below syntax:

ALTER TABLE Table_Name MODIFY (Column_Name1 Data_Type, Column_Name2 Data_Type);

Let's understand the ALTER TABEL MODIFY in SQL with examples.

Example of ALTER TABLE MODIFY column in SQL

We have taken multiple SQL examples to help you understand modifying single columns and multiple columns in the existing table using ALTER TABLE MODIFY statement.

Consider the following tables along with the given records.

Table 1: Mobile_Plan:

Package_Id	Data_Id	Talktime_Id
1	2	1
2	3	2
3	1	3

Table 2: Data_Plan:

Data_Id	Data_Limit	Data_Price
1	2	30
2	5	70
3	4	55

Table 3: Talktime_Plan:

Talktime_Id	Talktime _Limit	Talktime _Price
1	100	150
2	70	105
3	60	90

Table 4: Customer

CustomerId	Customer_Name	Phone_Number	Package_Id
101	Bhavesh	9846522021	1
102	Mahesh	7798598272	3
103	Anita	7865222021	1

Example 1: Write a query to update the size of the column Customer Name from the Customer Table.

ALTER TABLE Customer MODIFY Customer_Name varchar(30);

In the above query, we have modified the column Customer_Name size from 20 to 30. We have used ALTER TABLE statement with MODIFY keyword just after the table name to modify the column.

We will now use the DESC keyword and the table name to cross-check whether the column Customer_Name data type is modified in the Customer table.

DESC Customer;  

Field	Type	Null	Key	Default	Extra
CustomerId	int(11)	YES	PRI	NULL
Customer_Name	varchar(30)	YES		NULL
Phone_Number	varchar(10)	YES		NULL
Package_Id	Int(11)	YES	MUL	NULL

Example 2: Write a query to update the data type of the column Talktime_Limit and Talktime_Price from the Talktime_Plan Table.

ALTER TABLE Talktime_Plan MODIFY (Talktime_Limit varchar(2), Talktime_Price varchar(2));

We have modified the column Talktime_Limit and Talktime_Price data type from int to varchar in the above query. We have used ALTER TABLE statement with MODIFY keyword just after the table name to modify the column.

We will now use the DESC keyword and the table name to cross-check whether the column Talktime_Limit and Talktime_Price data type are modified or not in the Talktime_Plan table.

DESC Talktime_Plan; 

Field	Type	Null	Key	Default	Extra
TalkTime_Id	int(11)	YES	PRI	NULL
TalkTime_Limit	varchar(2)	YES		NULL
TalkTime_Price	varchar(2)	YES		NULL

ALTER TABLE DROP COLUMN statement in SQL

This is used when you delete the column from the existing table. Then in such a situation, rather than drop the entire table from the database, we can use the DROP keyword to drop the column.

Syntax of ALTER TABLE DROP COLUMN statement in SQL

ALTER TABLE Table_Name DROP Column_Name;   

Let's understand the ALTER TABEL DROP in SQL with examples.

Example of ALTER TABLE DROP column in SQL

We have taken SQL examples to help you understand modifying single columns and multiple columns in the existing table using ALTER TABLE DROP statement.

Consider the following tables along with the given records.

Table 1: Mobile_Plan:

Package_Id	Data_Id	Talktime_Id
1	2	1
2	3	2
3	1	3

Table 2: Data_Plan:

Data_Id	Data_Limit	Data_Price
1	2	30
2	5	70
3	4	55

Table 3: Talktime_Plan:

Talktime_Id	Talktime _Limit	Talktime _Price
1	100	150
2	70	105
3	60	90

Table 4: Customer

CustomerId	Customer_Name	Phone_Number	Package_Id
101	Bhavesh	9846522021	1
102	Mahesh	7798598272	3
103	Anita	7865222021	1

Example 1: Write a query to drop the Package_Id from the Customer table.

ALTER TABLE Customer DROP COLUMN Package_Id;

We have dropped the column name Package_Id from the Customer table in the above query. We have used ALTER TABLE DROP statement to drop the column from the table.

We will now cross_check whether the column Package_Id from the Customer table was deleted or not successfully.

SELECT * FROM CUSTOMER;

CustomerId	Customer_Name	Phone_Number
101	Bhavesh	9846522021
102	Mahesh	7798598272
103	Anita	7865222021

ALTER TABLE RENAME statement in SQL

Suppose we want to change the name of the column or table, then we will use RENAME keyword to rename the fields or tables of the existing table.

Syntax of ALTER TABLE RENAME statement in SQL

ALTER TABLE Table_Name RENAME Column_Name;   

Let's understand the ALTER TABEL RENAME in SQL with examples.

Example of ALTER TABLE RENAME column in SQL

We have taken SQL examples to help you understand modifying single columns and multiple columns in the existing table using ALTER TABLE RENAME statement.

Consider the following tables along with the given records.

CustomerId	Customer_Name	Phone_Number
101	Bhavesh	9846522021
102	Mahesh	7798598272
103	Anita	7865222021

Example 1: Write a query to change the name of the Customer table.

ALTER TABLE Customer RENAME TO CUST;

In the above query, we have renamed the table name of the Customer table to the Cust table. We used ALTER TABLE RENAME statement to rename the table name.

We will cross-check whether the table's name is changed or not successfully.

SHOW TABLES;

Example 1: Write a query to change the name of the column CustomerId to cid from the Cust table.

ALTER TABLE Cust RENAME COLUMN CustomerId cid int not null;

In the above query, we have changed the column name CustomerId to Cid. We have used ALTER TABLE RENAME statement to rename the table name.

We will cross-check whether the table's name is changed or not successfully.

SELECT * FROM CUST;