- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Databases
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
- SQL − ALTER TABLE Statement
- ALTER TABLE − ADD Column
- ALTER TABLE − DROP COLUMN
- ALTER TABLE − ADD INDEX
- ALTER TABLE − DROP INDEX
- ALTER TABLE − ADD PRIMARY KEY
- ALTER TABLE − DROP PRIMARY KEY
- ALTER TABLE − ADD CONSTRAINT
- ALTER TABLE − DROP CONSTRAINT
- ALTER TABLE − RENAME COLUMN
- ALTER TABLE − MODIFY DATATYPE
'; var adpushup = adpushup || {}; adpushup.que = adpushup.que || []; adpushup.que.push(function() { adpushup.triggerAd(ad_id); });
SQL − ALTER TABLE Statement
The SQL ALTER TABLE command is a part of Data Definition Language (DDL) and modifies the structure of a table. The ALTER TABLE command can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself.
The ALTER TABLE command can also change characteristics of a table such as the storage engine used for the table. We will make use of the following table in our examples
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | Kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | Hyderabad | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
Syntax
Following is the basic syntax of an ALTER TABLE command −
ALTER TABLE table_name [alter_option ...];
Where, the alter_option depends on the type of operation to be performed on a table. This article will discuss such important operations one by one.
ALTER TABLE − ADD Column
If you need to add a new column to a table, you should use the ADD COLUMN option along with ALTER TABLE statement as shown below −
ALTER TABLE table_name ADD column_name datatype;
Example
Following is the example to ADD a New Column to an existing table −
ALTER TABLE CUSTOMERS ADD SEX char(1);
Output
Executing the query above will produce the following output −
Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0
Verification
To verify whether the CUSTOMERS table is altered by adding a new column SEX, use the SELECT statement to retrieve the records of the table −
SELECT * FROM CUSTOMERS;
Now, the CUSTOMERS table will be displayed as follows −
| ID | NAME | AGE | ADDRESS | SALARY | SEX |
|---|---|---|---|---|---|
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 | NULL |
| 2 | Khilan | 25 | Delhi | 1500.00 | NULL |
| 3 | Kaushik | 23 | Kota | 2000.00 | NULL |
| 4 | Chaitali | 25 | Mumbai | 6500.00 | NULL |
| 5 | Hardik | 27 | Bhopal | 8500.00 | NULL |
| 6 | Komal | 22 | Hyderabad | 4500.00 | NULL |
| 7 | Muffy | 24 | Indore | 10000.00 | NULL |
ALTER TABLE − DROP COLUMN
If you need to drop an existing column from a table, you should use the DROP COLUMN option along with ALTER TABLE statement as shown below.
ALTER TABLE table_name DROP COLUMN column_name;
Example
Following is the example to DROP sex column from the existing table.
ALTER TABLE CUSTOMERS DROP COLUMN SEX;
Output
Executing the query above will produce the following output −
Query OK, 0 rows affected (0.07 sec)Records: 0 Duplicates: 0 Warnings: 0
Verification
To verify whether the CUSTOMERS table is altered by dropping an existing column SEX, use the SELECT statement to retrieve the records of the table −
SELECT * FROM CUSTOMERS;
Now, the CUSTOMERS table is changed and following would be the output from the SELECT statement.
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | Kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | Hyderabad | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
ALTER TABLE − ADD INDEX
You can add index to an existing column of a table using the ADD INDEX statement along with the ALTER statement −
ALTER TABLE table_name ADD INDEX index_name [index_type]
Example
Following query adds an index on the column NAME of CUSTOMERS table −
ALTER TABLE CUSTOMERS ADD INDEX name_index (NAME);
Output
The output will be displayed as −
Query OK, 0 rows affected (0.003 sec)Records: 0 Duplicates: 0 Warnings: 0
ALTER TABLE − DROP INDEX
You can drop an existing index from a table using the DROP INDEX statement along with the ALTER statement −
ALTER TABLE table_name DROP INDEX index_name;
Example
Following query adds an index on the column NAME of CUSTOMERS table −
ALTER TABLE CUSTOMERS DROP INDEX name_index;
Output
The output will be displayed as −
Query OK, 0 rows affected (0.003 sec)Records: 0 Duplicates: 0 Warnings: 0
ALTER TABLE − ADD PRIMARY KEY
Following is the syntax to add a primary key in an existing table of a database −
ALTER TABLE table_name ADD CONSTRAINT constraint_namePRIMARY KEY (column1, column2...);
Example
Before we add a primary key to an existing table, first let's create a new table called EMPLOYEES as follows:
CREATE TABLE EMPLOYEES( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2));
Following query adds primary key constraint on the column ID of EMPLOYEES table −
ALTER TABLE EMPLOYEES ADD CONSTRAINT MyPrimaryKey PRIMARY KEY(ID);
This will produce the following output −
Query OK, 0 rows affected, 1 warning (0.003 sec)Records: 0 Duplicates: 0 Warnings: 1
Verification
To verify the above query if you describe the table using the DESC EMPLOYEES command −
DESC EMPLOYEES;
This will display the structure of the table created: column names, their respective data types, constraints (if any) etc.
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| ID | int(11) | NO | PRI | NULL | |
| NAME | varchar(20) | NO | NULL | ||
| AGE | int(11) | NO | NULL | ||
| ADDRESS | char(25) | YES | NULL | ||
| SALARY | decimal(18,2) | YES | NULL |
ALTER TABLE − DROP PRIMARY KEY
Following is the syntax to delete a primary key from an existing table of a database −
ALTER TABLE table_name DROP PRIMARY KEY;
Example
Following query deletes primary key constraint from the column ID of EMPLOYEES table −
ALTER TABLE EMPLOYEES DROP PRIMARY KEY;
This will produce the following output −
Query OK, 0 rows affected, 1 warning (0.003 sec)Records: 0 Duplicates: 0 Warnings: 1
ALTER TABLE − ADD CONSTRAINT
Following is the syntax to add a unique constraint to a column of an existing table −
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(column1, column2...);
Example
Following query adds UNIQUE constraint to the table CUSTOMERS −
ALTER TABLE EMPLOYEES ADD CONSTRAINT CONST UNIQUE(NAME);
This will produce the following output −
Query OK, 0 rows affected (0.003 sec)Records: 0 Duplicates: 0 Warnings: 0
ALTER TABLE − DROP CONSTRAINT
Following is the syntax to drop a unique constraint from an existing table −
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
Example
Following query adds UNIQUE constraint to the table CUSTOMERS −
ALTER TABLE EMPLOYEES DROP CONSTRAINT CONST;
This will produce the following output −
Query OK, 0 rows affected (0.003 sec)Records: 0 Duplicates: 0 Warnings: 0
ALTER TABLE − RENAME COLUMN
Following is the syntax to rename a column name of an existing table −
ALTER TABLE table_name RENAME COLUMN old_column_name to new_column_name;
Example
Following query renames NAME column in table CUSTOMERS −
ALTER TABLE CUSTOMERS RENAME COLUMN name to full_name;
This will produce the following output −
Query OK, 0 rows affected (0.002 sec)Records: 0 Duplicates: 0 Warnings: 0
ALTER TABLE − MODIFY DATATYPE
Following is the syntax to change the data type of any column in MySQL, MS Server and Oracle.
SQL Server/MS Access Syntax
ALTER TABLE table_name ALTER COLUMN column_name datatype;
MySQL Syntax
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
Oracle Syntax
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
Example
Following query modifies datatype of SALARY column in MySQL CUSTOMERS table −
ALTER TABLE CUSTOMERS MODIFY COLUMN ID DECIMAL(18, 4);
This will produce the following output −
Query OK, 0 rows affected (0.003 sec)Records: 0 Duplicates: 0 Warnings: 0
Advertisem*nts
'; adpushup.triggerAd(ad_id); });
As an expert in SQL with demonstrable expertise and a depth of knowledge in relational database management systems (RDBMS) and SQL concepts, I'd like to dive into the ALTER TABLE statement, a crucial component of Data Definition Language (DDL) used for modifying the structure of database tables.
ALTER TABLE Statement: The SQL ALTER TABLE command is a powerful tool within DDL that allows you to make various modifications to a table's structure. These modifications include adding or deleting columns, creating or destroying indexes, changing the data type of existing columns, renaming columns or the table itself, and altering characteristics such as the storage engine used.
ALTER TABLE − ADD Column
If you need to add a new column to an existing table, you can use the ADD COLUMN option along with the ALTER TABLE statement. The syntax is as follows:
ALTER TABLE table_name ADD column_name datatype;Example:
ALTER TABLE CUSTOMERS ADD SEX char(1);This example adds a new column named SEX with a character data type to the existing CUSTOMERS table.
ALTER TABLE − DROP COLUMN
To drop an existing column from a table, you use the DROP COLUMN option along with the ALTER TABLE statement. The syntax is as follows:
ALTER TABLE table_name DROP COLUMN column_name;Example:
ALTER TABLE CUSTOMERS DROP COLUMN SEX;This example removes the column SEX from the CUSTOMERS table.
ALTER TABLE − ADD INDEX
Adding an index to an existing column can enhance database performance. The syntax is as follows:
ALTER TABLE table_name ADD INDEX index_name [index_type];Example:
ALTER TABLE CUSTOMERS ADD INDEX name_index (NAME);This example adds an index named name_index to the NAME column of the CUSTOMERS table.
ALTER TABLE − DROP INDEX
If you need to remove an existing index from a table, you can use the DROP INDEX statement along with the ALTER TABLE statement. The syntax is as follows:
ALTER TABLE table_name DROP INDEX index_name;Example:
ALTER TABLE CUSTOMERS DROP INDEX name_index;This example removes the index name_index from the CUSTOMERS table.
ALTER TABLE − ADD PRIMARY KEY
Adding a primary key to an existing table is essential for uniquely identifying each record. The syntax is as follows:
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2...);Example:
ALTER TABLE EMPLOYEES ADD CONSTRAINT MyPrimaryKey PRIMARY KEY(ID);This example adds a primary key constraint named MyPrimaryKey to the ID column of the EMPLOYEES table.
ALTER TABLE − DROP PRIMARY KEY
To remove a primary key constraint from an existing table, you can use the following syntax:
ALTER TABLE table_name DROP PRIMARY KEY;Example:
ALTER TABLE EMPLOYEES DROP PRIMARY KEY;This example removes the primary key constraint from the ID column of the EMPLOYEES table.
ALTER TABLE − ADD CONSTRAINT
Adding a unique constraint to a column ensures that all values in the column are distinct. The syntax is as follows:
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(column1, column2...);Example:
ALTER TABLE EMPLOYEES ADD CONSTRAINT CONST UNIQUE(NAME);This example adds a unique constraint named CONST to the NAME column of the EMPLOYEES table.
ALTER TABLE − DROP CONSTRAINT
To remove a unique constraint from an existing table, you can use the following syntax:
ALTER TABLE table_name DROP CONSTRAINT constraint_name;Example:
ALTER TABLE EMPLOYEES DROP CONSTRAINT CONST;This example removes the unique constraint CONST from the NAME column of the EMPLOYEES table.
ALTER TABLE − RENAME COLUMN
Renaming a column in an existing table is achieved with the following syntax:
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;Example:
ALTER TABLE CUSTOMERS RENAME COLUMN name TO full_name;This example renames the column name to full_name in the CUSTOMERS table.
ALTER TABLE − MODIFY DATATYPE
Changing the data type of a column can be necessary, and the syntax varies slightly between different database systems:
SQL Server/MS Access Syntax:
ALTER TABLE table_name ALTER COLUMN column_name datatype;MySQL Syntax:
ALTER TABLE table_name MODIFY COLUMN column_name datatype;Oracle Syntax:
ALTER TABLE table_name MODIFY COLUMN column_name datatype;Example:
ALTER TABLE CUSTOMERS MODIFY COLUMN ID DECIMAL(18, 4);This example changes the data type of the ID column in the CUSTOMERS table to DECIMAL(18, 4).
These examples demonstrate the versatility and power of the SQL ALTER TABLE statement, showcasing its ability to adapt and modify the structure of database tables to meet evolving requirements.
