MySQL – Unique Index
Table of content
- MySQL Unique Index
- Creating Unique Index on Multiple Columns
- Creating Unique Index Using a Client Program
”;
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.
”;