SQL ALTER Table

Filed Under: SQL

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.

  1. Adding a new column to the table
  2. Dropping a column from the table
  3. Changing data type of a column
  4. Add a constraint to a table
  5. 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.


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


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.


ALTER TABLE test.customer ADD customer_age int;

ALTER Table For Adding Column

SQL ALTER Table For Adding Column


 

2. Dropping a Column from Table

Syntax

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.

ALTER TABLE test.customer DROP COLUMN customer_age;
ALTER Table For Dropping Column

ALTER Table For Dropping Column

 

3. Changing Data Type of a Column

Syntax: –

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.

ALTER TABLE test.customer MODIFY COLUMN ProductId int;
ALTER Table For Modify Column

ALTER Table For Modify Column

 

4. Add a Constraint to a Table

Syntax: –

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.

ALTER TABLE test.customer ADD CONSTRAINT MyUniqueConstraint UNIQUE(CustomerName);
ALTER Table For Adding Constraint To Column

ALTER Table For Adding Constraint To Column

 

5. Remove Constraint from the Table

Syntax: –

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:- 

ALTER TABLE table_name DROP INDEX MyUniqueConstraint;

Example: –

Removing MyUniqueConstraint from Customer table.

If you are using MySQL following Query will help.

ALTER TABLE test.customer DROP INDEX MyUniqueConstraint;
ALTER Table For Removing Constraint To Column

ALTER Table For Removing Constraint To Column

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages