SQL UNIQUE Constraint


The SQL UNIQUE constraint is used to add unique or distinct value in a field and uniquely identifies each row in a database table.

The SQL UNIQUE constraint provide a guarantee for uniqueness for a field or multiple fields in a table.

Difference Between Unique Key and Primary Key

There are many differences between them, which is mentioned below:

Unique Key Primary Key
Unique Value Yes Yes
Null Value Support only once in a column Does not support null value
Column Limit Support multiple column can have unique key Support for only one column in a table
You can also search these topics using sql unique constraint, primary key vs unique key, unique key in sql, primary key unique, unique key and primary key, add unique constraint oracle, unique in sql query, can unique key be null, sql unique select, unique key constraint, sql server unique key, add constraint unique, oracle add unique constraint, sql server create unique index.

SQL UNIQUE Constraint on CREATE TABLE

The following SQL statement creates a SQL UNIQUE constraint on the "BookID" field when the "Books" table is created:

For Microsoft SQL Server / Oracle / Microsoft Access


CREATE TABLE Books
(
BookID INT NOT NULL UNIQUE,
BookName VARCHAR(255),
AuthorName VARCHAR(255),
BookPrice DECIMAL (8, 2)
);

For MySql


CREATE TABLE Books
(
BookID INT NOT NULL,
BookName VARCHAR(255),
AuthorName VARCHAR(255),
BookPrice DECIMAL (8, 2),
UNIQUE(BookID)
);

In the above example, the following SQL statement creates a new table called Books and adds five fields.

Here, BookID field has a SQL UNIQUE constraint to validate newly entered records for unique value.

Let's assume, if the "Books" table already contains the following records:

BookId BookName AuthorName BookPrice
111 Sql Complete Programming Suresh Babu 160
222 Pl Sql Quick Reference Siva Kumar 220
333 Sql Database Analysis Suresh Babu 180

Now executing the following below SQL statement:


INSERT INTO Books (BookID, BookName, AuthorName, BookPrice)
VALUES (222, 'Sql Quick Programming','Haris Babu', 260);

The above SQL statement will produce in an error, because '222' already exists in the BookID field, thus trying to insert another record with that value violates the SQL UNIQUE constraint.

To allow naming of a SQL UNIQUE constraint, and for defining a SQL UNIQUE constraint on multiple fields, use the following SQL syntax:

For Microsoft SQL Server / Oracle / Microsoft Access / MySql


CREATE TABLE Books
(
BookID INT NOT NULL,
BookName VARCHAR(255) NOT NULL,
AuthorName VARCHAR(255),
BookPrice DECIMAL (8, 2),
CONSTRAINT chk_BookID UNIQUE (BookID, BookName)
);


SQL UNIQUE Constraint on ALTER TABLE

To create a SQL UNIQUE constraint on the "BookID" field when the table is already created, use the following SQL statement:

For Microsoft SQL Server / Oracle / Microsoft Access / MySql


ALTER TABLE Books
ADD UNIQUE (BookID);

To create a SQL UNIQUE constraint on multiple fileds when the table is already created, use the following SQL statement:

For Microsoft SQL Server / Oracle / Microsoft Access / MySql


ALTER TABLE Books
ADD CONSTRAINT chk_BookID UNIQUE (BookID, BookName);


To DROP a SQL UNIQUE Constraint

To drop or remove a SQL UNIQUE constraint, use the following SQL statement:

For MySQL


ALTER TABLE Books
DROP INDEX chk_BookID;

For Microsoft SQL SERVER / Microsoft ACCESS / ORACLE


ALTER TABLE Books
DROP CONSTRAINT chk_BookID;

You can also search these topics using sql unique not null, add unique constraint sql, sql unique constraint example, composite unique constraint, unique constraint example, sql insert unique, oracle constraint unique, unique key example, create unique key sql server, unique keyword in sql, sql create table unique, oracle add constraint not null, is not null sql server, sql statement unique, sql unique command, sql query to select unique records.