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.

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

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

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

ALTER TABLE table_name
ADD UNIQUE (column_name);  

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

ALTER TABLE table_name
DROP INDEX uq_name; 

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.

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.

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.