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.
Advertisements