SQL UNIQUE

In this tutorial, we will learn how to use UNIQUE constraint using SQL.

SQL UNIQUE Constraint

• SQL UNIQUE constraint is used to ensure that no duplicate value is inserted in a column.
• In SQL, both UNIQUE and PRIMARY KEY constraint ensures that the uniqueness for a column or set of columns.
• The main difference between UNIQUE and PRIMARY KEY is that you can have multiple UNIQUE constraints per table but only one PRIMARY KEY constraint per table.

SQL UNIQUE Constraint on CREATE TABLE Statement

To add a UNIQUE constraint when a table is created, the statement is as follow:

SQL Server / Oracle

CREATE TABLE Employee(
    ID int NOT NULL UNIQUE,
    EmpName varchar(255) NOT NULL,
    City varchar(255),
    Age int,
    Salary decimal(18,2)
);

MySQL

CREATE TABLE Employee(
    ID int NOT NULL,
    EmpName varchar(255) NOT NULL,
    City varchar(255),
    Age int,
    Salary decimal(18,2),
    UNIQUE (ID)
);

Add UNIQUE Constraint on Multiple Column

To define a UNIQUE constraint on multiple columns, the statement is as follow:

MySQL / SQL Server / Oracle

CREATE TABLE Employee (
    ID int NOT NULL,
    EmpName varchar(255) NOT NULL,
    City varchar(255),
    Age int,
    Salary decimal(18,2),
    CONSTRAINT UC_Employee UNIQUE (ID,City)
);

SQL UNIQUE Constraint on ALTER TABLE Statement

MySQL / SQL Server / Oracle

To add a UNIQUE constraint after creating a table, the statement is as follow:

ALTER TABLE Employee
ADD UNIQUE (EmpName);

To add a UNIQUE constraint after creating a table on multiple columns, a statement is as follow:

ALTER TABLE Employee
ADD CONSTRAINT UC_Employee UNIQUE (ID,EmpName);

DROP UNIQUE Constraint

To drop a UNIQUE Constraint, a statement is as follow:

SQL Server / Oracle

ALTER TABLE tableName
DROP CONSTRAINT UC_constraintName;

ALTER TABLE Employee
DROP CONSTRAINT UC_Employee;

MySQL

ALTER TABLE Employee
DROP INDEX UC_Employee;