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
