MySQL - Unique Index

To create an unique index into MySQL table through a JavaScript program, we need to execute the CREATE UNIQUE INDEX statement using the query() function of mysql2 library as follows −

sql = "CREATE UNIQUE INDEX unique_ind ON CUSTOMERS (SALARY)";
con.query(sql);  

To create an unique index into MySQL table through a Java program, we need to execute the CREATE UNIQUE INDEX statement using the executeUpdate() function of JDBC as follows −

String sql = "CREATE UNIQUE INDEX UIID ON tutorials_tbl (tutorial_id)";
st.executeUpdate(sql);

To create an unique index into MySQL table through a Python program, we need to execute the CREATE UNIQUE INDEX statement using the execute() function of the MySQL Connector/Python as follows −

create_unique_index_query = "CREATE UNIQUE INDEX idx_unique_tutorial_id ON tutorials_tbl (tutorial_id)"
cursorObj.execute(create_unique_index_query)

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("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating table
  sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL,NAME VARCHAR(15) NOT NULL,AGE INT NOT NULL,ADDRESS VARCHAR(25),SALARY DECIMAL(10, 2),PRIMARY KEY(ID));"
  con.query(sql);

  //Inserting records
  sql = "INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (1, 'Ramesh', '32', 'Ahmedabad', 2000),(2, 'Khilan', '25', 'Delhi', 1500),(3, 'kaushik', '23', 'Kota', 2500),(4, 'Chaitali', '26', 'Mumbai', 6500),(5, 'Hardik','27', 'Bhopal', 8500),(6, 'Komal', '22', 'MP', 9000),(7, 'Muffy', '24', 'Indore', 5500);"
  con.query(sql);

  //Creating Unique Indexes
  sql = "CREATE UNIQUE INDEX unique_ind ON CUSTOMERS (SALARY)";
  con.query(sql);

  //Displaying list of indexes
  sql = "SHOW INDEX FROM CUSTOMERS;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**List of indexes:**")
    console.log(result)
  });
});  

Output

The output produced is as follows −

Connected!
--------------------------
**List of indexes:**
[
  {
    Table: 'customers',
    Non_unique: 0,
    Key_name: 'PRIMARY',
    Seq_in_index: 1,
    Column_name: 'ID',
    Collation: 'A',
    Cardinality: 7,
    Sub_part: null,
    Packed: null,
    Null: '',
    Index_type: 'BTREE',
    Comment: '',
    Index_comment: '',
    Visible: 'YES',
    Expression: null
  },
  {
    Table: 'customers',
    Non_unique: 0,
    Key_name: 'unique_ind',
    Seq_in_index: 1,
    Column_name: 'SALARY',
    Collation: 'A',
    Cardinality: 7,
    Sub_part: null,
    Packed: null,
    Null: 'YES',
    Index_type: 'BTREE',
    Comment: '',
    Index_comment: '',
    Visible: 'YES',
    Expression: null
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class UniqueIndex {
   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...!");

         //Create a unique index on the tutorials_tbl...!;
         String sql = "CREATE UNIQUE INDEX UIID ON tutorials_tbl (tutorial_id)";
         statement.executeUpdate(sql);
         System.out.println("Unique Index created Successfully...!");

         //showing the indexes...!
         ResultSet resultSet = statement.executeQuery("SHOW INDEXES FROM tutorials_tbl");
         System.out.println("Following are the indexes in tutorials_tbl");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+ " "+ resultSet.getString(2)
                    +" "+resultSet.getString(3)+" " + resultSet.getString(4)
                    +" " + resultSet.getString(4));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}      

Output

The output obtained is as shown below −

Connected successfully...!
Unique Index created Successfully...!
Following are the indexes in tutorials_tbl
tutorials_tbl 0 PRIMARY 1 1
tutorials_tbl 0 UIID 1 1  

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
create_unique_index_query = "CREATE UNIQUE INDEX idx_unique_tutorial_id ON tutorials_tbl (tutorial_id)"
cursorObj.execute(create_unique_index_query)
connection.commit()
print('unique index created successfully.')
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

unique index created successfully.