MySQL – 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 −
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);
To Alter a table From MySQL Database through a Node.js program we need to execute the Alter statement using the query() function of the mysql2 library as −
sql = "ALTER TABLE table_name"; con.query(sql);
To Alter a table From MySQL Database through a Java program we need to execute the Alter statement using the JDBC function executeUpdate() as −
String sql = "ALTER TABLE table_name"; statement.execute(sql);
To Alter a table From MySQL Database through a Python program we need to execute the Alter statement using the execute() function of the MySQL Connector/Python as −
sql = "ALTER TABLE table_name"; cursorObj.execute(sql);
Example
Following are the programs −
$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.
var mysql = require(''mysql2''); var con = mysql.createConnection({ host: "localhost", user: "root", password: "Nr5a0204@123" }); //Connecting to MySQL con.connect(function (err){ if (err) throw err; console.log("Connected!"); console.log("--------------------------"); sql = "USE TUTORIALS" con.query(sql); //Altering a table sql = "ALTER TABLE testalter_tbl DROP i"; con.query(sql, function(err){ if (err) throw err console.log("Altered table successfully..."); }); });
Output
The output produced is as follows −
Connected! -------------------------- Altered table successfully...
import java.sql.*; public class AlterTable { public static void main(String[] args){ String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String username = "root"; String password = "password"; try{ Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection(url, username, password); Statement statement = connection.createStatement(); System.out.println("Connected successfully...!"); //Alter table statement...! String sql = "ALTER TABLE testalter_tbl DROP i"; statement.executeUpdate(sql); System.out.println("Table altered successfully...!"); connection.close(); } catch(Exception e){ System.out.println(e); } } }
Output
The output obtained is as shown below −
Connected successfully...! Table altered successfully...!
import mysql.connector #establishing the connection connection = mysql.connector.connect( host=''localhost'', user=''root'', password=''password'', database=''tut'' ) table_name = ''testalter_tbl'' # ALTER TABLE statement alter_statement = ''testalter_tbl DROP i'' #Creating a cursor object cursorObj = connection.cursor() cursorObj.execute(f"ALTER TABLE {table_name} {alter_statement}") print(f"Table ''{table_name}'' is altered successfully.") cursorObj.close() connection.close()
Output
Following is the output of the above code −
Table ''testalter_tbl'' is altered successfully.