MySQL - Unique Key

A Unique Key in MySQL, when applied on a certain field of a database table, does not allow duplicate values to be inserted in that column, i.e. it is used to uniquely identify a record in a table.

Usually, any relational database contains a lot of information stored in multiple tables and each table holds a huge number of records. When we are handling such huge amounts of data there is a chance of redundancy (duplicate records). SQL keys are a way to handle this issue.

This Unique Key works as an alternative to the Primary Key constraint; as both unique and primary keys assure uniqueness in a column of a database table.

Creating MySQL Unique Key

We can create a Unique Key on a MySQL table column using the UNIQUE keyword, and it holds the following features −

• Even though unique key is similar to the primary key in a table, it can accept a single NULL value unlike the primary key.
• It cannot have duplicate values.
• It can also be used as a foreign key in another table.
• A table can have more than one Unique column.

Syntax

Following is the syntax to create a UNIQUE key constraint on a column in a table −

CREATE TABLE table_name(
   column_name1 datatype UNIQUE, 
   column_name2 datatype,
   ...
);

As you observe, we just need to specify the keyword UNIQUE after the name of the desired column while creating a table using CREATE TABLE statement.

Example

In this example, let us create a table named CUSTOMERS and define a UNIQUE Key on one of its fields, ADDRESS. Look at the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25) UNIQUE,
   SALARY DECIMAL (18, 2)
);

Output

The table structure displayed will contain a UNI index on the ADDRESS column as shown −

As you can see in the table definition, the Unique Key is created on the ADDRESS field.

Creating Multiple Unique Keys

We can create one or more Unique Key constraints on a column in a single MySQL table. When this constraint is applied in multiple fields, one cannot insert duplicate values in those fields.

Syntax

Following is the syntax to create unique key constraints on multiple columns in a table −

CREATE TABLE table_name(column_name1 UNIQUE, column_name2 UNIQUE,...)

Example

Assume we have created another table with the name CUSTOMERS in the MySQL database using CREATE TABLE statement.

Here we are creating a UNIQUE constraint on columns NAME and ADDRESS using the UNIQUE keyword as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL UNIQUE,
   AGE INT NOT NULL,
   ADDRESS CHAR (25) UNIQUE,
   SALARY DECIMAL (18, 2)
);

Output

The table structure displayed will contain a UNI index on the ADDRESS column as shown −

Creating Unique Key on Existing Columns

We can add a unique key constraint on an existing column of a table using the ALTER TABLE... ADD CONSTRAINT statement.

Syntax

Following is the syntax to create a UNIQUE Key on existing columns of a table −

ALTER TABLE table_name 
ADD CONSTRAINT unique_key_name 
UNIQUE (column_name);

Note − Here the UNIQUE_KEY_NAME is just the name of the Unique Key. It is optional to specify the name while creating a unique key. It is used to drop the constraint from the column in a table.

Example

Using the ALTER TABLE statement, you can add a UNIQUE constraint on any existing column in the CUSTOMERS table created previously. In the following example, we are applying the UNIQUE constraint on the NAME column as shown below −

ALTER TABLE CUSTOMERS 
ADD CONSTRAINT UNIQUE_NAME 
UNIQUE (NAME);

Output

The table structure displayed will contain a UNI index on the ADDRESS column as shown −

Dropping MySQL Unique Key

If there is an unique constraint on a column already, you can drop it whenever it is not needed. To drop the Unique Constraint from the column of a table you need to use the ALTER TABLE statement again.

Syntax

Following is the SQL query to drop the UNIQUE constraint from the column of a table −

ALTER TABLE TABLE_NAME DROP CONSTRAINT UNIQUE_KEY_NAME;

Example

In this example, we will drop the constraint named UNIQUE_NAME from the column NAME of the CUSTOMERS table using the following MySQL query −

ALTER TABLE CUSTOMERS DROP CONSTRAINT UNIQUE_NAME;

Output

The table structure displayed will contain a UNI index only on the ADDRESS column, referring that the index on NAME column is removed.

Creating Unique Key Using Client Program

In addition to use a key on a column to identify uniquely using the MySQL query We can also apply a Unique Key constraint on a Field using a client program.

Syntax

$sql = 'CREATE TABLE customers(cust_ID INT NOT NULL UNIQUE, cust_Name VARCHAR(30))';
$mysqli->query($sql);

sql = "CREATE TABLE customers(cust_ID INT NOT NULL, cust_Name VARCHAR(30), cust_login_ID INT AUTO_INCREMENT, PRIMARY KEY(cust_login_ID))";
con.query(sql);  

String sql = "CREATE TABLE customers(Cust_ID INT NOT NULL UNIQUE, Cust_Name VARCHAR(30))";
statement.execute(sql);

unique_key_query = 'CREATE TABLE TEST1 (ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, MOBILE BIGINT UNIQUE, AADHAR BIGINT UNIQUE, AGE INT NOT NULL)'
cursorObj.execute(unique_key_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.
');

$sql = 'CREATE TABLE customers(cust_ID INT NOT NULL UNIQUE, cust_Name VARCHAR(30))';
if ($mysqli->query($sql)) {
    echo "Unique column created successfully in customers table \n";
}
if ($mysqli->errno) {
    printf("Table could not be created!.
", $mysqli->error);
}
$mysqli->close();

Unique column created successfully in customers table   

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  console.log("Connected successfully...!");
  console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //create a table that stores primary key!
  sql = "CREATE TABLE customers(cust_ID INT NOT NULL, cust_Name VARCHAR(30), cust_login_ID INT AUTO_INCREMENT, PRIMARY KEY(cust_login_ID))";
  con.query(sql);
  
  //describe table details
  sql = "DESCRIBE TABLE customers";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});    

[
    {
      id: 1,
      select_type: 'SIMPLE',
      table: 'customers',
      partitions: null,
      type: 'ALL',
      possible_keys: null,
      key: null,
      key_len: null,
      ref: null,
      rows: 1,
      filtered: 100,
      Extra: null
    }
]

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

public class UniqueKey {
   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 key in the customer table...!;
         String sql = "CREATE TABLE customers(Cust_ID INT NOT NULL UNIQUE, Cust_Name VARCHAR(30))";
         statement.execute(sql);
         System.out.println("Unique key created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE customers");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2)+" "
                    +resultSet.getString(3)+ " "+ resultSet.getString(4));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}   

Connected successfully...!
Unique key created successfully...!
Cust_ID int NO PRI null
Cust_Name varchar(30) YES  null

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
# Create table 
unique_key_query = 'CREATE TABLE TEST1 (ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, MOBILE BIGINT UNIQUE, AADHAR BIGINT UNIQUE, AGE INT NOT NULL)'
cursorObj.execute(unique_key_query)
connection.commit()
print("Unique key column is created successfully!")
cursorObj.close()
connection.close()   

Unique key column is created successfully!