UNIQUE Constraint in SQL

UNIQUE Constraint enforces a column or set of columns to have unique values. If a column has a Unique constraint, it means that particular column cannot have duplicate values in a table.

Set UNIQUE Constraint while creating a table

For SQL Server / MS Access / Oracle:

Syntax:

CREATE TABLE <table_name>
(
    <column_name> <data_type> UNIQUE,
    <column_name2> <data_type>,
    ....
    ....
);

Example:

Here we are setting up the UNIQUE Constraint for two columns: STU_NAME & STU_ADDRESS. which means these two columns cannot have duplicate values.

Note: STU_NAME column has two constraints (NOT NULL and UNIQUE both) setup.

CREATE TABLE STUDENTS(
       ROLL_NO   INT           NOT NULL,
       STU_NAME VARCHAR (35)   NOT NULL UNIQUE,
       STU_AGE  INT            NOT NULL,
       STU_ADDRESS  VARCHAR (35) UNIQUE,     
       PRIMARY KEY (ROLL_NO)
);

MySQL:

Syntax:

CREATE TABLE <table_name>
(
    <column_name> <data_type>,
    <column_name2> <data_type>,
    ....
    ....
    UNIQUE(column_name)
);

Example:

Setting up constraint on STU_NAME column.

CREATE TABLE STUDENTS(
       ROLL_NO   INT           NOT NULL,
       STU_NAME VARCHAR (35)   NOT NULL,
       STU_AGE  INT            NOT NULL,
       STU_ADDRESS  VARCHAR (35), 
       UNIQUE(STU_NAME),    
       PRIMARY KEY (ROLL_NO)
);

Naming of UNIQUE Constraint:

MySQL / SQL Server / MS Access / Oracle:

CREATE TABLE STUDENTS(
  ROLL_NO INT NOT NULL,
  STU_NAME VARCHAR (35) NOT NULL,
  STU_AGE INT NOT NULL,
  STU_ADDRESS VARCHAR (35),
  CONSTRAINT stu_Info UNIQUE(STU_NAME, STU_ADDRESS),
  PRIMARY KEY (ROLL_NO)
);

Set UNIQUE Constraint on a already created table

For MySQL / Oracle / SQL Server / MS Access:

For single column and without constraint naming:

Syntax:

ALTER TABLE <table_name>
ADD UNIQUE (<column_name>);

Example:

ALTER TABLE STUDENTS
ADD UNIQUE (STU_NAME);

For multiple columns and with constraint naming:

Syntax:

ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name> UNIQUE (<column_name1>, <column_name2>,...);

Example:

ALTER TABLE STUDENTS
ADD CONSTRAINT stu_Info UNIQUE (STU_NAME,STU_ADDRESS);

How to drop a UNIQUE Constraint

IN MySQL:

syntax:

ALTER TABLE <table_name>
DROP INDEX <constraint_name>;

Example:

ALTER TABLE STUDENTS
DROP INDEX stu_Info

IN ORACLE / SQL Server / MS Access:

Syntax:

ALTER TABLE <table_name>
DROP CONSTRAINT <constraint_name>;

Example:

ALTER TABLE STUDENTS
DROP CONSTRAINT stu_Info;