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;