We have discussed how to create a table in the database, but there are cases when we would like to modify the table that was created. In order to modify a table SQL provides ALTER as the keyword. We will try to understand the usage of the ALTER keyword for altering a table in detail.
SQL ALTER Table
There are cases when we designed and created a table based on the existing need of the application. Consider in future the requirement is changed and we need to make modifications in the existing table. In such cases, we would be using the ALTER keyword. Let us now consider the following scenarios for further discussion.
- Adding a new column to the table
- Dropping a column from the table
- Changing data type of a column
- Add a constraint to a table
- Remove constraint from the table.
Note: All the scenarios and queries mentioned below are for MySQL database.
We will consider the following Customer table for further discussion.
|
1 2 3 |
CREATE TABLE `test`.`customer` ( `CustomrId` INT NOT NULL, `CustomerName` VARCHAR(45) NULL, `ProductId` VARCHAR(45) NOT NULL, `State` VARCHAR(45) NULL, PRIMARY KEY (`CustomrId`, `ProductId`), UNIQUE INDEX `CustomrId_UNIQUE` (`CustomrId` ASC) VISIBLE); |
1. Adding a New Column to the table
Syntax
|
1 2 3 |
ALTER TABLE table_name ADD column_name datatype; |
In the syntax above, the ALTER TABLE is the keyword that tells the database that a modification is needed in the table and the ADD keyword tells that a column addition needs to be done.
Example
Adding a customer_age column of int datatype in the Customer table.
|
1 2 3 |
ALTER TABLE test.customer ADD customer_age int; |
SQL ALTER Table For Adding Column
2. Dropping a Column from Table
Syntax
|
1 |
ALTER TABLE table_name DROP COLUMN column_name; |
The DROP COLUMN keyword tell that a column needs to be deleted.
Example: –
Removing customer_age column of int datatype in the Customer table.
|
1 |
ALTER TABLE test.customer DROP COLUMN customer_age; |
ALTER Table For Dropping Column
3. Changing Data Type of a Column
Syntax: –
|
1 |
ALTER TABLE table_name MODIFY COLUMN column_name datatype; |
MODIFY COLUMN keyword tells the database that a column needs to be modified.
Example: –
Changing the data type of productId from Varchar to Int.
|
1 |
ALTER TABLE test.customer MODIFY COLUMN ProductId int; |
ALTER Table For Modify Column
4. Add a Constraint to a Table
Syntax: –
|
1 |
ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...columnN); |
ADD CONSTRAINT keyword tells the database that a constraint needs to be added in the table. The UNIQUE keyword tells that the column after the UNIQUE keyword will have unique values in it.
Example: –
Adding MyUniqueConstraint to the Customer table.
|
1 |
ALTER TABLE test.customer ADD CONSTRAINT MyUniqueConstraint UNIQUE(CustomerName); |
ALTER Table For Adding Constraint To Column
5. Remove Constraint from the Table
Syntax: –
|
1 |
ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint; |
In the syntax above, the DROP CONSTRAINT keyword tells the database that a constraint needs to be removed from the table.
Syntax for MySQL:-
|
1 |
ALTER TABLE table_name DROP INDEX MyUniqueConstraint; |
Example: –
Removing MyUniqueConstraint from Customer table.
If you are using MySQL following Query will help.
|
1 |
ALTER TABLE test.customer DROP INDEX MyUniqueConstraint; |
ALTER Table For Removing Constraint To Column