How To Use UNIQUE Constraint In SQL

In this article, we will learn how to use the UNIQUE constraint in SQL.

The UNIQUE constraint is used to ensure that all values in a column are different.

Like PRIMARY KEY constraint, UNIQUE constraint provides a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint has a UNIQUE constraint automatically. However, we can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

 

UNIQUE – CREATE TABLE:

Please check How To Create Table Using Query In SQL, to get a brief description of the CREATE TABLE statement.

Example-1

The subsequent statement would create a UNIQUE constraint on the “ID” column when the “Article” table is created:

CREATE TABLE Article (
    ID int UNIQUE,
    Title varchar(100),
    Author varchar(100),
    Views int 
);

Example-2

The subsequent statement would create a UNIQUE constraint on the “ID” and “Title” columns when the “Article” table is created:

CREATE TABLE Article (
    ID int,
    Title varchar(100),
    Author varchar(100),
    Views int,
    CONSTRAINT UCArticle UNIQUE (ID, Title)
);

 

UNIQUE – ALTER TABLE:

Please check How To Alter Table Using Query In SQL, to get a brief description of the ALTER TABLE statement.

• To ADD a UNIQUE Constraint

Syntax

ALTER TABLE TABLE_NAME 
ADD CONSTRAINT CONSTRAINT_NAME UNIQUE (column1, column2, ...columnN);

Example

The subsequent statement would add a UNIQUE constraint on the “ID” and “Title” columns using ALTER TABLE statement:

ALTER TABLE Article 
ADD CONSTRAINT UCArticle UNIQUE (ID, Title);

• To DROP a UNIQUE Constraint

Syntax

ALTER TABLE TABLE_NAME 
DROP CONSTRAINT CONSTRAINT_NAME;

Example

The subsequent statement would delete a UNIQUE constraint named “UCArticle” using ALTER TABLE statement:

ALTER TABLE Article 
DROP CONSTRAINT UCArticle;

 

Also, check How To Use NOT NULL Constraint In SQL