MySQL Unique Key

A unique key in MySQL is a single field or combination of fields that ensure all values going to store into the column will be unique. It means a column cannot stores duplicate values. For example, the email addresses and roll numbers of students in the "student_info" table or contact number of employees in the "Employee" table should be unique.

MySQL allows us to use more than one column with UNIQUE constraint in a table. It can accept a null value, but MySQL allowed only one null value per column. It ensures the integrity of the column or group of columns to store different values into a table.

Needs of Unique Key

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

• It stores only distinct values that maintain the integrity and reliability of the database for accessing the information in an organized way.

• It also works with a foreign key in preserving the uniqueness of a table.

• It can contain null value into the table.

Syntax :

The following syntax is used to create a unique key in MySQL.

If we want to create only one unique key column into a table, use the syntax as below:

CREATE TABLE table_name(  
    col1 datatype,  
    col2 datatype UNIQUE,  
    ...  
);  

If we want to create more than one unique key column into a table, use the syntax as below:

CREATE TABLE table_name(  
  col1 col_definition,  
  col2 col_definition,  
  ...  
  [CONSTRAINT constraint_name]  
  UNIQUE(column_name(s))  
);  

If we have not specified the name for a unique constraint, MySQL generates a name for this column automatically. So, it is recommended to use the constraint name while creating a table.

For example :

Let's move on to Primary Key in MySQL

Share this page on :

FaceBook Whatsapp Twitter