UNIQUE Constraint
The UNIQUE constraint is used to ensure that all values in a column are different. It prevents duplicate values from being stored in a column.
What is a UNIQUE Constraint?
A UNIQUE constraint ensures that no two rows contain the same value in the specified column.
It is commonly used for columns such as Email, Phone Number, Username or other values that should remain unique.
Simple Example
If Email has a UNIQUE constraint, two students cannot have the same email address.
UNIQUE Constraint Rules
- Duplicate values are not allowed.
- UNIQUE constraints maintain data uniqueness.
- A table can have multiple UNIQUE constraints.
- A UNIQUE constraint can be applied to one or more columns.
- A UNIQUE constraint is different from a PRIMARY KEY.
Create UNIQUE Constraint
A UNIQUE constraint can be created while defining a table.
CREATE TABLE Students
(
StudentId INT PRIMARY KEY,
StudentName VARCHAR(100),
Email VARCHAR(150) UNIQUE
);
Named UNIQUE Constraint
We can also provide a specific name to the UNIQUE constraint.
CREATE TABLE Students
(
StudentId INT PRIMARY KEY,
StudentName VARCHAR(100),
Email VARCHAR(150),
CONSTRAINT UQ_Students_Email
UNIQUE (Email)
);
Insert Unique Values
Different email addresses can be inserted successfully.
INSERT INTO Students
VALUES (101, 'Rahul', 'rahul@gmail.com');
INSERT INTO Students
VALUES (102, 'Priya', 'priya@gmail.com');
Duplicate Value Example
Suppose the email already exists in the table.
INSERT INTO Students
VALUES (103, 'Amit', 'rahul@gmail.com');
Add UNIQUE Constraint to Existing Table
A UNIQUE constraint can be added to an existing table using ALTER TABLE.
ALTER TABLE Students
ADD CONSTRAINT UQ_Students_Email
UNIQUE (Email);
Multiple UNIQUE Constraints
A table can contain multiple UNIQUE constraints.
CREATE TABLE Users
(
UserId INT PRIMARY KEY,
Username VARCHAR(50),
Email VARCHAR(150),
CONSTRAINT UQ_Users_Username
UNIQUE (Username),
CONSTRAINT UQ_Users_Email
UNIQUE (Email)
);
Example Output
| StudentId | StudentName | |
|---|---|---|
| 101 | Rahul | rahul@gmail.com |
| 102 | Priya | priya@gmail.com |
Every Email value is unique in the table.
UNIQUE Constraint Structure
Students Table
|
↓
Email Column
|
↓
UNIQUE Constraint
|
↓
Duplicate Values
❌
|
↓
Unique Values
✓
PRIMARY KEY vs UNIQUE
| PRIMARY KEY | UNIQUE |
|---|---|
| Uniquely identifies each row. | Ensures values are unique. |
| Only one PRIMARY KEY per table. | Multiple UNIQUE constraints can exist. |
| Does not allow NULL values. | NULL handling is different from PRIMARY KEY. |
| Usually identifies the main record. | Used for additional unique values. |
Important Points
- UNIQUE prevents duplicate values.
- Multiple UNIQUE constraints can be created in a table.
- UNIQUE constraints can be named explicitly.
- UNIQUE constraints can be added using ALTER TABLE.
Summary
In this section, we learned how the UNIQUE constraint prevents duplicate values, how to create it during table creation and how to add it to an existing table.