Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

Alter Tables

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

ALTER Command
MySQL ALTER Command
The MySQL ALTER command is used to modify the structure of an existing table. It allows you to make various changes, such as adding, deleting, or modify columns within the table.
Additionally, the ALTER command is also used to add and drop different constraints associated with an existing table.
Since this command modifies the structure of a table, it is a part of Data Definition Language in SQL. This is also where the ALTER command differs from UPDATE command; while ALTER interacts with the structure of a table to modify it, UPDATE only interacts with the data present in the table without disturbing its structure.
Syntax
Following is the syntax of ALTER command in MySQL 
ALTER TABLE table_name [alter_option ...];
Example
Let us begin with the creation of a table named CUSTOMERS.
CREATE TABLE CUSTOMERS (
   ID INT,
   NAME VARCHAR(20)
);
Now, execute the following query to display information about the columns in CUSTOMERS table.
SHOW COLUMNS FROM CUSTOMERS;
Output
Following are the details of the columns of the CUSTOMERS table 
Field
Type
Null
Key
Default
Extra
ID
int
YES
﻿
NULL
﻿
NAME
varchar(20)
YES
﻿
NULL
﻿
Dropping a Column
To drop a column in an existing table, we use the ALTER TABLE command with DROP clause.
Example
In the following example, we are dropping an existing column named ID from the above-created CUSTOMERS table 
ALTER TABLE CUSTOMERS DROP ID;
Output
Executing the query above will produce the following output 
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
Verification
To verify whether the ID column has been dropped from the CUSTOMERS table, execute the following query 
SHOW COLUMNS FROM CUSTOMERS;
As we can see in the output below, there is no ID column present. Hence it is dropped.
Field
Type
Null
Key
Default
Extra
NAME
varchar(20)
YES
﻿
NULL
﻿
Note: A DROP clause will not work if the column is the only one left in the table.
Adding a Column
To add a new column into an existing table, we use ADD keyword with the ALTER TABLE command.
Example
In the following query, we are adding a column named ID into an existing table CUSTOMERS.
ALTER TABLE CUSTOMERS ADD ID INT;
Output
Executing the query above will produce the following output 
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
Now, the CUSTOMERS table will contain the same two columns that it had when you first created the table. But the newly added ID column will be added at the end of the table by default. In this case, it will add after the NAME column.
Verification
Let us verify using the following query 
SHOW COLUMNS FROM CUSTOMERS;
As we can see in the output below, the newly added ID column is inserted at the end of the table.
Field
Type
Null
Key
Default
Extra
NAME
varchar(20)
YES
﻿
NULL
﻿
ID
int
YES
﻿
NULL
﻿
Repositioning a Column
If we want a column to be placed at a specific position within the table, we can use FIRST to make it the first column or AFTER col_name to indicate that the new column should be positioned after the col_name.
Example
Consider the previously modified CUSTOMERS table, where the NAME is the first column and ID is the last column.
In the following query, we are removing the ID column from the table and then adding it back, positioning it as the first column in the table using FIRST keyword 
ALTER TABLE CUSTOMERS DROP ID;
ALTER TABLE CUSTOMERS ADD ID INT FIRST;
Output
Executing the query above will produce the following output 
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
Verification
Now, let us verify the positions of the column in the CUSTOMERS table 
SHOW COLUMNS FROM CUSTOMERS;
As we can see in the output below, the ID column is positioned first.
Field
Type
Null
Key
Default
Extra
ID
int
YES
﻿
NULL
﻿
NAME
varchar(20)
YES
﻿
NULL
﻿
Example
Here, we are removing the ID column from the table and then adding it back, positioning it after the NAME column using the AFTER col_name keyword.
ALTER TABLE CUSTOMERS DROP ID;
ALTER TABLE CUSTOMERS ADD ID INT AFTER NAME;
Output
Executing the query above will produce the following output 
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
Verification
Now, let us verify the positions of the column in the CUSTOMERS table 
SHOW COLUMNS FROM CUSTOMERS;
As we can see in the output below, the ID column is positioned first.
Field
Type
Null
Key
Default
Extra
NAME
varchar(20)
YES
﻿
NULL
﻿
ID
int
YES
﻿
NULL
﻿
Note: The FIRST and AFTER specifiers work only with the ADD clause. This means that if you want to reposition an existing column within a table, you first must DROP it and then ADD it at the new position.
Altering a Column Definition or a Name
In MySQL, to change a column's definition, we use MODIFY or CHANGE clause in conjunction with the ALTER command.
Example
In the query below, we are changing the definition of column NAME from varchar(20) to INT using the MODIFY clause 
ALTER TABLE CUSTOMERS MODIFY NAME INT;
Output
Executing the query above will produce the following output 
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
Verification
Now, let us verify the definition of the NAME column in the CUSTOMERS table 
SHOW COLUMNS FROM CUSTOMERS;
As we can observe, the definition for NAME column has been changed to INT.
Field
Type
Null
Key
Default
Extra
NAME
int
YES
﻿
NULL
﻿
ID
int
YES
﻿
NULL
﻿
Example
We can also change the column definition using CHANGE, but the syntax is a bit different from MODIFY. After the CHANGE keyword, we specify the name of the column (twice) that we want to change, then specify the new definition.
Here, we are changing the definition of column ID from INT to varchar(20) using the CHANGE clause 
ALTER TABLE CUSTOMERS MODIFY ID VARCHAR(20);
Output
Executing the query above will produce the following output 
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
Verification
Now, let us verify the definition of the NAME column in the CUSTOMERS table 
SHOW COLUMNS FROM CUSTOMERS;
As we can observe, the definition for NAME column has been changed to INT.
Field
Type
Null
Key
Default
Extra
NAME
int
YES
﻿
NULL
﻿
ID
varchar(20)
YES
﻿
NULL
﻿
Altering a Column's Default Value
In MySQL, we can change a default value for any column by using the DEFAULT constraint with ALTER command.
Example
In the following example, we are changing the default value of NAME column.
ALTER TABLE CUSTOMERS ALTER NAME SET DEFAULT 1000;
Output
Executing the query above will produce the following output 
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
Verification
Now, let us verify the default value of the NAME column in the CUSTOMERS table 
SHOW COLUMNS FROM CUSTOMERS;
As we can observe, the default value for NAME column has been changed to 1000.
Field
Type
Null
Key
Default
Extra
NAME
int
YES
﻿
1000
﻿
ID
varchar(20)
YES
﻿
NULL
﻿
Example
We can remove the default constraint from any column by using DROP clause along with the ALTER command.
Here, we are removing the default constraint of NAME column.
ALTER TABLE CUSTOMERS ALTER NAME DROP DEFAULT;
Output
Executing the query above will produce the following output 
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
Verification
Now, let us verify the default value of the NAME column in the CUSTOMERS table
SHOW COLUMNS FROM CUSTOMERS;
As we can observe, the default value for NAME column has been changed to NULL.
Field
Type
Null
Key
Default
Extra
NAME
int
YES
﻿
NULL
﻿
ID
varchar(20)
YES
﻿
NULL
﻿
Altering (Renaming) a Table
To rename a table, use the RENAME option of the ALTER TABLE statement.
Example
The following query renames the table named CUSTOMERS to BUYERS.
ALTER TABLE CUSTOMERS RENAME TO BUYERS;
Output
Executing the query above will produce the following output 
Query OK, 0 rows affected (0.02 sec)
Verification
Now, let us verify the default value of the NAME column in the CUSTOMERS table 
SHOW COLUMNS FROM BUYERS;
The table has been renamed to BUYERS, as we can see from the columns within it.
Field
Type
Null
Key
Default
Extra
NAME
int
YES
﻿
NULL
﻿
ID
varchar(20)
YES
﻿
NULL
﻿
Altering Table Using a Client Program
Besides altering an existing table in a MySQL database with a MySQL query, we can also use a client program to perform the ALTER TABLE operation.
Syntax
Following are the syntaxes of Altering a table from MySQL Database in various programming languages −
PHPNodeJSJavaPython
To Alter a table From MySQL Database through a PHP program, we need to execute the Alter statement using the mysqli function query() as 
$sql = "ALTER TABLE table_name";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
﻿
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.');
﻿
$sql = "ALTER TABLE testalter_tbl  DROP i";
if ($mysqli->query($sql)) {
    printf("table altered successfully.");
}
if ($mysqli->errno) {
    printf("table could not alter: %s", $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as follows 
table altered successfully.
Pr
﻿
﻿
﻿
﻿
﻿

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

ALTER Command

MySQL ALTER Command

Syntax

Example

Output

Dropping a Column

Example

Output

Verification

Adding a Column

Example

Output

Verification

Repositioning a Column

Example

Output

Verification

Example

Output

Verification

Altering a Column Definition or a Name

Example

Output

Verification

Example

Output

Verification

Altering a Column's Default Value

Example

Output

Verification

Example

Output

Verification

Altering (Renaming) a Table

Example

Output

Verification

Altering Table Using a Client Program

Syntax

Example

Output