Unique Key

06 May 2024

|
4 min read
Blog Image

A unique key in SQL Server is a constraint that ensures all the values in a column or a combination of columns are unique across the table. Unlike the primary key, a table can have multiple unique keys. Unique keys help maintain data integrity by preventing duplicate values in the specified columns.

Characteristics of a Unique Key

  1. Uniqueness: Each value in the unique key column(s) must be unique across the table.
  2. Null Values: Unique keys allow one NULL value per column in the constraint (this behavior can vary slightly based on SQL Server versions and settings).
  3. Multiple Unique Keys: A table can have more than one unique key, whereas it can have only one primary key.

Purpose of Unique Keys

  • Data Integrity: Ensures that the data in the unique key column(s) is unique, preventing duplicate entries.
  • Search Performance: Indexes created by unique constraints can improve search performance.
  • Alternate/Candiate Keys: Acts as an alternate key when primary key constraints are not suitable for certain columns.

Creating Unique Keys

1. Using T-SQL During Table Creation

Single Column Unique Key:

CREATE TABLE Employees (

   EmployeeID INT PRIMARY KEY,

   Email NVARCHAR(100) UNIQUE,  -- Unique constraint on Email

   FirstName NVARCHAR(50),

   LastName NVARCHAR(50),

   HireDate DATE,

   Salary DECIMAL(10, 2)

);

In this example, the Email column must contain unique values for each row in the Employees table.

Composite Unique Key:

CREATE TABLE Orders (

   OrderID INT PRIMARY KEY,

   ProductID INT,

   OrderDate DATE,

   Quantity INT,

   CONSTRAINT UQ_OrderProduct UNIQUE (OrderID, ProductID)  -- Composite unique
constraint

);

Here, the combination of OrderID and ProductID must be unique across the Orders table.

From Reader to Expert: Join Our SQL Server Developer Course Today!

2. Using T-SQL to Add Unique Key to an Existing Table

Single Column Unique Key:

ALTER TABLE Employees

ADD CONSTRAINT UQ_Employees_Email UNIQUE (Email);

This adds a unique constraint named UQ_Employees_Email to the Email column in the Employees table.

Composite Unique Key:

ALTER TABLE Orders

ADD CONSTRAINT UQ_Orders_OrderProduct UNIQUE (OrderID, ProductID);

This adds a unique constraint named UQ_Orders_OrderProduct to the combination of OrderID and ProductID columns in the Orders table.

Managing Unique Keys

Dropping a Unique Key Constraint:

ALTER TABLE Employees

DROP CONSTRAINT UQ_Employees_Email;

This removes the unique constraint UQ_Employees_Email from the Email column in the Employees table.

Examples of Using Unique Keys

Example 1: Ensuring Unique Email Addresses

CREATE TABLE Users (

   UserID INT PRIMARY KEY,

   Username NVARCHAR(50),

   Email NVARCHAR(100) UNIQUE,  -- Unique constraint ensures no duplicate emails

   PasswordHash VARBINARY(64)

);

-- Attempting to insert duplicate emails will fail

INSERT INTO Users (UserID, Username, Email, PasswordHash)

VALUES (1, 'user1', 'user1@example.com', 0x123456);


INSERT INTO Users (UserID, Username, Email, PasswordHash)

VALUES (2, 'user2', 'user1@example.com', 0x789ABC);  -- This will fail due to unique constraint violation

Enroll in our Data Analyst courses in Toronto to gain proficiency in data visualization, statistical analysis, and data-driven decision-making.

Example 2: Ensuring Unique Combinations of OrderID and ProductID

CREATE TABLE OrderDetails (

   OrderID INT,

   ProductID INT,

   Quantity INT,

   CONSTRAINT UQ_OrderDetails_OrderProduct UNIQUE (OrderID, ProductID)  -- Composite unique constraint

);

-- Attempting to insert duplicate OrderID and ProductID combinations will fail

INSERT INTO OrderDetails (OrderID, ProductID, Quantity)

VALUES (1, 100, 5);

INSERT INTO OrderDetails (OrderID, ProductID, Quantity)

VALUES (1, 100, 10);  -- This will fail due to unique constraint violation


Using SQL Server Management Studio (SSMS)

1. Creating a Unique Key:

  • Open SSMS and connect to the SQL Server instance.
  • In Object Explorer, navigate to the database and table.
  • Right-click on the table and select "Design".
  • Right-click on the column you want to set as unique and select "Indexes/Keys...".
  • In the "Indexes/Keys" dialog, click "Add", set the "Type" to "Unique Key", and configure the columns.
  • Save the table design by clicking the save icon or pressing Ctrl+S.

2. Adding a Unique Key to an Existing Table:

  • In Object Explorer, expand the database and the table.
  • Right-click the table and select "Design".
  • Right-click the column you want to set as unique and select "Indexes/Keys...".\
  • In the "Indexes/Keys" dialog, click "Add", set the "Type" to "Unique Key", and configure the columns.
  • Save the changes.
Take Your Skills Further with TrainingHub.io - Sign Up for Our SQL Server Developer Course Today! Join thousands of learners in Canada who have advanced their careers with TrainingHub.io’s IT online courses.

Recent blogs

Featured Image
GROUP BY Clause

21 June 2024

|
3 min read
Featured Image
CASE Statement

15 June 2024

|
4 min read
Featured Image
ORDER BY Clause

10 June 2024

|
3 min read