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
- Uniqueness: Each value in the unique key column(s) must be unique across the table.
- 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).
- 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.
Suggested blogs
Recent blogs
