Unique Key in SQL

DevSniper
Published : 28 December 2024

166 Views

Unique Key is a database constraint that ensures all the values in a column or a combination of columns are distinct. It prevents two records from having the same values in a unique Key column or set.

Both Unique Key and Primary Key ensure uniqueness in a table. The main differences are that a Unique Key allows NULL values but requires each non-null value to be unique and multiple unique keys can exist in a table while a Primary Key can not allow NULL value and only one primary key is allowed per table.

Single Column Unique Key

A single-column unique key ensures that values in one column are unique. This is typically used to prevent duplicate entries in a column where data redundancy is not acceptable.

Syntax :-

CREATE TABLE TableName(
   column1 datatype UNIQUE KEY,
   column2 datatype,
   .....
);

CREATE TABLE TableName (
   column1 datatype, 
   column2 datatype,
   ...
   UNIQUE KEY (column1)
);

CREATE TABLE TableName(
   column1 datatype UNIQUE KEY,
   column2 datatype UNIQUE KEY,
   column3 datatype,
   .....
);

CREATE TABLE TableName (
   column1 datatype,
   column2 datatype,
   column3 datatype,
   ...
   UNIQUE KEY (column1),
   UNIQUE KEY (column2)
);

Example

Using above SQL query,Let's create an Employee table with column EmployeeId,Name, Mobile, JobTitle, Department and salary. To ensure no two employees can have the same mobile number, we are creating a Unique Key constraint on column Mobile.

CREATE TABLE Employees (
    EmployeeId INT PRIMARY KEY ,
    Name VARCHAR(50) NOT NULL,
    Mobile VARCHAR(20) UNIQUE KEY,
    JobTitle VARCHAR(100) NOT NULL,
    Department VARCHAR(100) NOT NULL,
    Salary DECIMAL(10, 2)
);

Verification

Since we have created Employees table with Unique key constraints on Mobile column so we can not enter duplicate Mobile in the table. So if we attempt to insert a duplicate Mobile number into the table, the database will throw an error.

INSERT INTO Employees VALUES
(1,"Ram", "9876543212","SDE","Tech",4.5),
(2,"Rajesh", "9876543212","OM","OPR",7.5);

Error Code: 1062. Duplicate entry '9876543212' for key 'Employee.Mobile' 0.016 sec

Composite Unique Key

A composite unique key (or multi-column unique key) is created by combining multiple columns. it ensures uniqueness across the combined column data. This type is typically used in scenarios where the combination of two or more columns must be unique.

Syntax :-

CREATE TABLE TableName (
   column1 datatype,
   column2 datatype,
   column3 datatype,
   ...
   UNIQUE KEY (column1, column3)
);

Example

Using above SQL query, Let's create an Employees table with column EmployeeId, Name, Mobile, JobTitle, Department and Salary. To ensure no two employees can have the same combination of Name and Mobile, we are creating a Composite Unique Key constraint on column Name and Mobile.

CREATE TABLE Employees (
    EmployeeId INT PRIMARY KEY,
    Name VARCHAR(50),
    Mobile VARCHAR(20),
    JobTitle VARCHAR(100) ,
    Department VARCHAR(100),
    Salary DECIMAL(10, 2),
    UNIQUE KEY (Name, Mobile)
);

Verification

Since we have created Employees table with Composite Unique key constraints on Name and Mobile columns. So if we are attempt to insert unique Name and Mobile number separately we can do this.

Inserting duplicate entry of Mobile column

INSERT INTO EmployeeV2 VALUES
(1,"jack", "9876543212","SDE","Tech",4.5),
(2,"Head", "9876543212","OM","OPR",7.5);

There will be no error in above query execution both records will be inserted in table sucessfully

Let's take other case, inserting duplicate name in Name column

INSERT INTO Employees VALUES
(1,"Ram", "9876543211","SDE","Tech",4.5),
(2,"Ram", "9876543213","OM","OPR",7.5);

The above query also executed successfully without any problem.

Going forward, if we attempt to insert a duplicate combination of Name and Mobile, the database will throw an error.

INSERT INTO Employees VALUES
(1,"Ram", "9876543211","SDE","Tech",4.5),
(2,"Ram", "9876543211","OM","OPR",7.5);

Error Code: 1062. Duplicate entry 'Ram-9876543211' for key 'Employees.Name' 0.032 sec

Unique Key on an Existing Column

If you want to create Unique Key on an existing table/column. You can do this by following below syntax.

ALTER TABLE TableName
ADD CONSTRAINT UNIQUE_KEY_NAME UNIQUE (column(s));

If you attempt to add a unique key constraint to a column that already contains duplicate values, the database will throw an error.

For example, consider a case where you created Employees table without any unique key constraints and inserted the following records.

INSERT INTO Employees VALUES
(1,"Ram", "9876543211","SDE","Tech",4.5),
(2,"Ram", "9876543211","OM","OPR",7.5);

Now, if you try to add a unique key constraint on the Name column, the database will throw an error because the column contains duplicate value for "Ram"

ALTER TABLE Employees
ADD CONSTRAINT UNIQUE_KEY_NAME UNIQUE (Name);

Error Code: 1062. Duplicate entry 'Ram' for key 'Employees.UNIQUE_KEY_NAME' 0.047 sec

Dropping an SQL Unique Key

If you want to drop Unique Key from table. You can do this by following below syntax.

Dropping by Unique Key Constraint name

ALTER TABLE TableName DROP CONSTRAINT UNIQUE_KEY_NAME;

Dropping by Unique Key Column name

ALTER TABLE TableName DROP INDEX ColumnName;

Handling NULL Values in Unique Key Constraints

Handling NULL values in Unique Key constraint can vary across different SQL databases, Different SQL databases have different rules regarding how many NULL values can exist in a column with a unique constraint.

SQL Server :- Allows only one NULL value for columns with a unique constraint.
MySQL :- Allows multiple NULL values.
PostgreSQL :- Allows multiple NULL values in columns with unique constraints

Key Points of Unique Key

Unique Key constraint is crucial for maintaining data integrity by ensuring the uniqueness of data.
A table can have more than one unique key.
It can also be used as a foreign key in another table.
Avoid using the Unique Key on columns which values may change over time.
When we create a unique key on a column or set of columns, the database automatically creates a unique index to ensure the uniqueness of the values.
A unique index is similar to a regular index but with the additional rule that no two rows can have the same value in the indexed column(s).