Understanding UNIQUE Key in SQL with Examples

UNIQUE Key in SQL

In database management, ensuring data integrity is crucial. One of the key constraints that helps maintain data accuracy is the UNIQUE Key. This constraint ensures that the values in specified columns are distinct across the table.

Complete Python Course with Advance topics:-Click Here
SQL Tutorial :-Click Here

What is a UNIQUE Key?

A UNIQUE Key is a constraint that enforces the uniqueness of one or more columns in a table. While it shares some similarities with a Primary Key, there are key differences:

✅ A UNIQUE Key can accept one NULL value (unlike a Primary Key, which cannot accept NULL values).
✅ A table can have multiple UNIQUE Keys but only one Primary Key.

Why Use a UNIQUE Key?

Using a UNIQUE Key helps prevent duplicate entries and maintains data consistency. It’s especially useful when certain columns require distinct values but aren’t the primary identifiers.

Creating a UNIQUE Key Constraint on Table Creation

To define a UNIQUE Key during table creation, use the following syntax:

SQL Server / Oracle / MS Access Example (Single Column):

CREATE TABLE employees 
(  
    Emp_ID INT NOT NULL UNIQUE,  
    Emp_Name VARCHAR(255) NOT NULL,  
    Department VARCHAR(100),  
    City VARCHAR(100)  
);  

MySQL Example (Single Column):

CREATE TABLE employees 
(  
    Emp_ID INT NOT NULL,  
    Emp_Name VARCHAR(255) NOT NULL,  
    Department VARCHAR(100),  
    City VARCHAR(100),  
    UNIQUE (Emp_ID)  
);  

Multiple Column UNIQUE Key Constraint:

CREATE TABLE employees 
(  
    Emp_ID INT NOT NULL,  
    Emp_Name VARCHAR(255) NOT NULL,  
    Department VARCHAR(100),  
    City VARCHAR(100),  
    CONSTRAINT uc_Employee UNIQUE (Emp_ID, Emp_Name)  
);  

Adding a UNIQUE Key Constraint Using ALTER TABLE

If your table is already created and you need to add a UNIQUE Key constraint afterward, use the ALTER TABLE statement:

Single Column Example:

ALTER TABLE employees 
ADD UNIQUE (Emp_ID);  

Multiple Column Example:

ALTER TABLE employees 
ADD CONSTRAINT uc_Employee UNIQUE (Emp_ID, Emp_Name);  

Dropping a UNIQUE Key Constraint

If you need to remove a UNIQUE constraint, you can use the following syntax:

For MySQL:

ALTER TABLE employees  
DROP INDEX uc_Employee;  

For SQL Server / Oracle / MS Access:

ALTER TABLE employees  
DROP CONSTRAINT uc_Employee;  

Key Differences Between UNIQUE Key and Primary Key

Download New Real Time Projects :-Click here
Complete Advance AI topics:- CLICK HERE

Conclusion

The UNIQUE Key constraint is an essential tool for maintaining data integrity. By enforcing unique values in specific columns, it safeguards your database from duplicate entries while allowing flexibility with NULL values. Understanding its proper use ensures your database design is robust and efficient.

For more expert database insights, stay tuned to UpdateGadh!

unique key in sql with example
unique key vs primary key
foreign key in sql
unique key in sql w3schools
unique key in sql server
select unique in sql
unique key in sql oracle
unique key in sql can be null