MySQL UNIQUE Constraint

The UNIQUE constraint is used to ensure that the column values are unique without duplicate.
We can have any number of columns being declared with UNIQUE constraint.

Example

   create table employee(empno int NOT NULL, 
        name varchar(50) NOT NULL,
        age numeric, 
        role varchar(50) NOT NULL, 
        location varchar(50) NOT NULL, 
        salary decimal NOT NULL,
        UNIQUE (empno)
        );

In the above query we use the UNIQUE constraint to set the empno to accept only unique values.

If we need to provide multiple columns with UNIQUE constraint then we can use the following syntax.

create table employee(empno int NOT NULL, 
name varchar(50) NOT NULL, 
age numeric, 
role varchar(50) NOT NULL, 
location varchar(50) NOT NULL, 
salary decimal NOT NULL, 
CONSTRAINT UC_employee UNIQUE (empno,name));

ALTER TABLE - UNIQUE Constraint

In order to add UNIQUE constraint to the existing column, then we can use ALTER TABLE with UNIQUE constraint.

Example

alter table employee
add unique (empno);

If we need to set multiple columns with UNIQUE constraint then we can use the following syntax

 alter table employee
 CONSTRAINT UC_employee UNIQUE (empno,name);

The above query will set UNIQUE constraint to the empno and name column.


Most Read