MysQL – Unique Key

MySQL unique keys are single fields or combinations of fields that ensure that all values stored in a column are unique. A column cannot store duplicate values. A unique email address and roll number should be contained in the “student” table or a unique contact number should be contained in the “employee” table, for example.

It is possible to use more than one column with a UNIQUE constraint in a table in MySQL. The column can accept a null value, however MySQL only allows one null value per column. It ensures the integrity of a column or group of columns when storing different values in a table.

• It is useful for preventing two records from storing identical values in the column.

• The database contains only distinct values that maintain the integrity and reliability of the database so that information can be accessed effectively.
• A foreign key can also be used to preserve the uniqueness of a table.
• In the table, null values may be present.

Syntax

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
  CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)
);

table_name –  You will need to provide the name of the table you wish to create.

column1, column2 – You may create as many columns as you wish in the table.

constraint_name – This is the name of the unique constraint.

uc_col1, uc_col2, … uc_col_n – The columns that form the unique constraint.

Example

Creating a table “employees” with a UNIQUE constraint:

CREATE TABLE employees (  
  emp_no int AUTO_INCREMENT PRIMARY KEY,  
  Name varchar(45) DEFAULT NULL,  
  Email varchar(45) NOT NULL UNIQUE 
);

In the output below, we can see that the first INSERT query executes correctly, but the second statement fails with the error message: Duplicate entry ‘1’ for key Student.Email.

INSERT IGNORE INTO  employees  
VALUES (4,'Mounika', 'Mounika@gmail.com'),   
(5, 'Vinay', 'ravi@gmail.com');  

select * from employees;