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

Search Keys

  • sql unique constraint
  • primary key vs unique key
  • unique key in sql
  • unique constraint
  • unique sql
  • unique key
  • select unique
  • sql where unique
  • primary key unique
  • unique key and primary key
  • add unique constraint oracle
  • unique postgresql
  • unique in sql query
  • can unique key be null
  • sql unique select
  • unique sql query
  • unique key constraint
  • sql server unique key
  • unique column
  • 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;


Search Keys

  • sql unique not null
  • add unique constraint sql
  • pl sql unique
  • sql unique constraint example
  • composite unique constraint
  • unique constraint example
  • sql insert unique
  • drop unique key
  • oracle constraint unique
  • unique key example
  • unique field
  • add unique key
  • create unique key sql server
  • unique keyword in sql
  • sql create table unique
  • sql unique id
  • oracle add constraint not null
  • is not null sql server
  • sql statement unique
  • sql unique command
  • sql query to select unique records
  • unique index sql