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