UNIQUE constraint ensures that all values in a specific column is different. UNIQUE key does not allow duplicate values. UNIQUE key allows NULL values but does not allow NULL values multiple times.

We can create multiple UNIQUE columns on one table however only one PRIMARY KEY for table. Defining primary key on a column has a UNIQUE constraint property by default. Both UNIQUE and PRIMARY KEY constraints provide uniqueness for a column or set of columns.

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.