Summary -

In this topic, we described about the below sections -

Why UNIQUE constraint?

UNIQUE constraint is used to maintain identical values in same column. One table may have multiple UNIQUE constraint columns. UNIQUE constraint column allows NULL values, but UNIQUE constraint column have only one NULL value itself.

How to create UNIQUE constraint?

We can create UNIQUE constraint in two ways. Those are -

  • Using CREATE TABLE Statement.
  • Using ALTER TABLE Statement.

Syntax for creating unique key while creating table – [MySQL]

CREATE TABLE table_name ( 
    column1 datatype,
    column2 datatype,
    [ CONSTRAINT uq_name]
    required_column datatype UNIQUE);  

Syntax for creating unique key while creating table – [SQL Server / Oracle / MS Access]

CREATE TABLE table_name ( 
   column1 datatype UNIQUE,
   column2 datatype,
      … 
      …                        
   columnN datatype);  

Syntax for creating unique key while creating table with multiple columns – [MySQL / SQL Server / Oracle / MS Access]

CREATE TABLE table_name ( 
     column1 datatype,
     column2 datatype,
     [CONSTRAINT uq_name] 
     UNIQUE (required_column1, required_column2, ….., .. )); 

Syntax for creating unique key on single column of existing table – [MySQL / SQL Server / Oracle / MS Access]

ALTER TABLE table_name
ADD UNIQUE (column_name);  

Syntax for creating unique key on multiple columns of existing table – [MySQL / SQL Server / Oracle / MS Access]

ALTER TABLE table_name
ADD [CONSTRAINT uq_name]
UNIQUE (column1, column2);  

Syntax for deleting unique key on existing table – [MySQL]

ALTER TABLE table_name
DROP INDEX uq_name; 

Syntax for deleting unique key on existing table – [SQL Server / Oracle / MS Access]

ALTER TABLE table_name
DROP CONSTRAINT uq_name; 

  • uq_name - Represents user defined name of the unique key.
  • table_name – Represents on which table user want to create unique key.
  • required_column- Represents on which column included in unique key.

Examples -

Scenario – Creating unique key while creating a table.

Requirement – Creating emp_id as unique key while creating employee_details table. The query was as follows -

CREATE TABLE Employee 
 (emp_id INT NOT NULL UNIQUE,
  emp_name VARCHAR (20), 
  dept_id  INT); 

By executing above query, emp_id column was created as unique key on Employee_details table.


Scenario – Creating unique key on existing table .

Requirement – Creating uq_emp_name as unique key on existing employee_details table which does not have a primary key column. The query was as follows -

ALTER TABLE employee_details
  ADD CONSTRAINT uq_emp_name
  UNIQUE (emp_name);  

By executing above query, emp_name was created as unique key on employee_details table.


Note:- While creating unique key on existing table make sure that column which is going to add unique key has not null constraint and does not have the duplicate values.

Scenario – Creating unique key with multiple columns while creating a table.

Requirement – Creating emp_id, emp_name as unique key while creating employee_details table. The query was as follows -

CREATE TABLE Employee ( 
     emp_id INT NOT NULL,
     emp_name VARCHAR (20),
     dept_id  INT, 
  CONSTRAINT uq_employee
  UNIQUE (emp_id, emp_name)); 

By executing above query, we can create uq_employee as unique key on employee_details table with multiple columns emp_id, emp_name.


Scenario – Creating unique key on existing table with multiple columns.

Requirement – Creating emp_id as unique key on existing employee_details table. The query was as follows -

ALTER TABLE employee_details
ADD CONSTRAINT uq_employee
UNIQUE (emp_id, emp_name);   

By executing above query, we can create uq_employee as unique key on existing employee_details table with multiple columns emp_id, emp_name.


Scenario – Deleting existing unique key.

Requirement – Deleting existing uq_emp_name unique key on employee_details table. The query was as follows -

ALTER TABLE employee_details
 DROP CONSTRAINT uq_emp_name; 

By executing above query, we can delete existing primary key on employee_details table.