Home » MySQL

MySQL UNIQUE Key

MySQL | UNIQUE Key: Learn about the MySQL UNIQUE Key, with its explanation, syntax, and query examples.
Submitted by Apurva Mathur, on September 15, 2022

UNIQUE Key

As the name suggests, if the column is made as a UNIQUE key which means it will only contain unique values.

Now the question arises that if the same task is performed by a unique key then what is the need for this? Is the Primary key and UNIQUE key both are same? No, UNIQUE key and PRIMARY both are different in many ways as a table can only contain one primary key which should be AI (auto incremented), but when it comes to a UNIQUE key, no such validation is there. UNIQUE keys can be more than one in a table. The UNIQUE key tells us that all the data which will be inserted in this column should be unique.

For example, we know that everybody has a unique Aadhaar number, and no two persons can have the same Aadhaar number, in such a case, we can make the Aadhaar number column a UNIQUE key so that it only has unique values. One big difference between the UNIQUE key and the PRIMARY key is that it is not important to key the UNIQUE key as AI (auto incremented), as characters can be written here and characters can never be set as AI.

How to create UNIQUE key in a table?

Suppose we are asked to create a table named "student_details" and inside this table, we have to make 6 columns named as, student_id (this should be primary key as well as Auto incremented), student_name, student_department, year, gender, phone_number (UNIQUE key), and marks.

So to make any column as the UNIQUE key we follow the syntax given below;

UNIQUE Key Syntax

CREATE TABLE student_details(
  student_ID INT AUTO_INCREMENT PRIMARY KEY, 
  student_name VARCHAR(45) NULL, 
  student_department VARCHAR(45) NULL, 
  year INT NULL, 
  gender VARCHAR(45) NULL, 
  marks VARCHAR(45) NULL, 
  phone_number VARCHAR(45) UNIQUE
);

This command will create a table in which student_id will be the primary key, AI and phone number column will be UNIQUE.

Apply PRIMARY key and AI (Auto incremented) constraints in the MySQL Workbench

Step 1: Select the schemas, in which you want to create your table,

Step 2: After selecting the schema, Go to tables, and click on CREATE TABLE.

Step 3: After clicking on create the table, give table name and column names,

Step 4: Then select the right side check boxes, here I am making student_ID as my Primary key and also AI, so I will click on PK and AI, and phone_number as my Unique key, so for this I will click on UQ.

Step 5: After step 4, just click on Apply and Done.

Your table is created which has a Primary key and a Unique key.

---