Khóa học miễn phí MySQL – Unique Index nhận dự án làm có lương

MySQL – Unique Index

Table of content


MySQL Indexes are used to return the data from the database real quick. The users cannot see the indexes performing, instead they are just used to speed up the queries.

However a unique index, in addition to speeding up data retrieval queries, is also used to maintain data integrity in a table. When a unique index is defined on a table column, we cannot add any duplicate values into that column.

MySQL Unique Index

A unique index can be created on one or more columns of a table using the CREATE UNIQUE INDEX statement in MySQL.

  • If we are creating unique index on only a single column, all the rows in that column must be unique.
  • We cannot create a unique index where NULL values are present in multiple rows in a single column.
  • If we are creating unique index on multiple columns, the combination of rows in those columns must be unique.
  • We cannot create a unique index on multiple columns if the combination of columns contains NULL values in more than one row.

Syntax

Following is the syntax for creating a unique index in MySQL −

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ..., columnN);

Example

Let us first create a table named CUSTOMERS using the following query −

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

In the following query, we are inserting some values in to the above created table using the INSERT statement −

INSERT INTO CUSTOMERS 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);

The table will be created as follows −

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

Now, create a unique index for the column named SALARY in the CUSTOMERS table using the following query −

CREATE UNIQUE INDEX unique_ind ON CUSTOMERS (SALARY);

Inserting Duplicate Values

Now, let us try to update the value in the SALARY column with a duplicate (already existing data) value using the following query −

UPDATE CUSTOMERS SET SALARY = 2000 WHERE ID = 2;

Error

The above query results in an error because a column that has unique index cannot contain duplicate values in it.

ERROR 1062 (23000): Duplicate entry ''2000.00'' for key ''customers.unique_ind''

Creating Unique Index on Multiple Columns

In MySQL, we can also create a unique index on multiple columns of a table using the CREATE UNIQUE INDEX statement. To do so, you just need to pass the name of the columns (you need to create the index on) to the query.

Example

Assume the previously created CUSTOMERS table and create a unique index on the columns named NAME and AGE using the following query −

CREATE UNIQUE INDEX mul_unique_index ON CUSTOMERS(NAME, AGE);

Verification

Using the following query, we can list all the indexes that are created on the CUSTOMERS table −

SHOW INDEX FROM CUSTOMERSG

The table of index information is displayed as −

*************************** 1. row ***********************
        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
*************************** 2. row ***********************
        Table: customers
   Non_unique: 0
     Key_name: mul_unique_index
 Seq_in_index: 1
  Column_name: NAME
    Collation: A
  Cardinality: 7
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 3. row ***********************
        Table: customers
   Non_unique: 0
     Key_name: mul_unique_index
 Seq_in_index: 2
  Column_name: AGE
    Collation: A
  Cardinality: 7
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 4. row ***********************
        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

Creating Unique Index Using a Client Program

In addition to creating an index using a MySQL query, we can also create the unique index using a client program.

Syntax

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

$sql = "CREATE UNIQUE INDEX uidx_tid ON tutorials_table (tutorial_id)";
$mysqli->query($sql);

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)

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.
''); // UNIQUE INDEX $sql = "CREATE UNIQUE INDEX uidx_tid ON tutorials_table (tutorial_id)"; if ($mysqli->query($sql)) { printf("Unique Index created successfully!.
"); } if ($mysqli->errno) { printf("Index could not be created!.
", $mysqli->error); } $mysqli->close();

Output

The output obtained is as follows −

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

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

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc