Unique Key

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.