UNIQUE Constraint makes sure all values in a column are exclusive.
PRIMARY KEY Constraint is also had the same property as UNIQUE Constraint. But there are some differences.
In a table only one PRIMARY KEY Constraint is possible but we can use many UNIQUE KEY Constraint per table. Another difference is PRIMARY KEY Constraint cannot accept a NULL value. UNIQUE KEY Constraint accepts a NULL value.
Syntax for single column UNIQUE KEY Constraint:-
CREATE TABLE table_name(
…,
column_name data_type UNIQUE,
…
);
The syntax for multi[le column UNIQUE KEY Constraint:-
CREATE TABLE table_name(
…,
column_name1 data_type ,
column_name2 data_type
…
CONSTRAINT constraint_name UNIQUE (column_name1, column_name2)
);
Note:- If we cannot define UNIQUE Constraint name then MySql will automatically generate UNIQUE Constraint name.
Example :-
For single column UNIQUE Constraint:-
CREATE TABLE Employee (
ID int NOT NULL,
LastName varchar(255),
FirstName varchar(255),
Age int,
Department varchar(255),
Email varchar(255) NOT NULL,
UNIQUE (ID)
);
For multiple column UNIQUE Constraint:-
CREATE TABLE Employee (
ID int NOT NULL,
LastName varchar(255),
FirstName varchar(255),
Age int,
Department varchar(255),
Email varchar(255) NOT NULL,
CONSTRAINT UC_Employee UNIQUE (ID,Email)
);
Sometimes, If we are not defining UNIQUE Constraint at the time of creating a table then we can define UNIQUE Constraint using ALTER TABLE syntax.
For Single column:-
ALTER TABLE Employee ADD UNIQUE (ID);
For Multiple Column:-
ALTER TABLE Employee ADD CONSTRAINT UC_Employee UNIQUE (ID, Email);
DROP UNIQUE CONSTRAINT from a table:-
ALTER TABLE Employee DROP INDEX UC_ Employee;