Understanding UNIQUE Key in SQL with Examples

UNIQUE Key in SQL

Ensuring data integrity is crucial in database design. The UNIQUE Key constraint enforces that values in specified columns are distinct across the table, preventing duplicate entries.

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

What is a UNIQUE Key?

A UNIQUE Key shares similarities with a Primary Key but has key differences:

• A UNIQUE Key can accept one NULL value (a Primary Key cannot).
• A table can have multiple UNIQUE Keys but only one Primary Key.

Creating a UNIQUE Key on Table Creation

-- SQL Server / Oracle / MS Access
CREATE TABLE employees (
  Emp_ID INT NOT NULL UNIQUE,
  Emp_Name VARCHAR(255) NOT NULL,
  Department VARCHAR(100)
);

-- MySQL (using UNIQUE keyword separately)
CREATE TABLE employees (
  Emp_ID INT NOT NULL,
  Emp_Name VARCHAR(255) NOT NULL,
  UNIQUE (Emp_ID)
);

Multi-Column UNIQUE Constraint

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

Adding a UNIQUE Key with ALTER TABLE

ALTER TABLE employees ADD UNIQUE (Emp_ID);

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

Dropping a UNIQUE Key

-- MySQL
ALTER TABLE employees DROP INDEX uc_Employee;

-- SQL Server / Oracle / MS Access
ALTER TABLE employees DROP CONSTRAINT uc_Employee;

UNIQUE Key vs Primary Key

• NULL: UNIQUE allows one NULL; Primary Key does not.
• Count: Many UNIQUE keys per table; only one Primary Key.
• Duplicates: Neither allows duplicates.

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

Conclusion

The UNIQUE Key constraint protects your database from duplicate entries while still allowing one NULL where flexibility is needed. For more database insights, stay tuned to .

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 can be null