A primary key is a fundamental concept in database design, critical for ensuring the integrity and uniqueness of data in a table. Here's a detailed explanation of primary keys, their characteristics, and examples of how to create and manage them in SQL Server.
Characteristics of a Primary Key
1. Uniqueness: Each value in the primary key column(s) must be unique across the table.
2. Non-null: Primary key columns cannot contain NULL values.
3. Single Column or Composite: A primary key can consist of a single column or multiple columns (composite key).
Importance of Primary Keys
- Identifying Rows: Each row in the table can be uniquely identified using the primary key.
- Referential Integrity: Primary keys are used in relationships to enforce referential integrity through foreign keys.
- Performance: Primary keys often have indexes created automatically, which can improve query performance.
Examples of Creating Primary Keys
1. Single Column Primary Key
Creating a Table with a Single Column Primary Key:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY, -- Single column primary key
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
HireDate DATE,
Salary DECIMAL(10, 2)
);
In this example, EmployeeID is the primary key, ensuring each employee has a unique identifier.
Adding a Primary Key to an Existing Table:
ALTER TABLE Employees
ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID);
Here, the primary key constraint PK_Employees is added to the EmployeeID column of the Employees table.
2. Composite Primary Key
Creating a Table with a Composite Primary Key:
CREATE TABLE OrderDetails (
OrderID INT,
ProductID INT,
Quantity INT,
PRIMARY KEY (OrderID, ProductID) -- Composite primary key
);
In this case, OrderID and ProductID together form the primary key, ensuring that each combination of order and product is unique.
Adding a Composite Primary Key to an Existing Table:
ALTER TABLE OrderDetails
ADD CONSTRAINT PK_OrderDetails PRIMARY KEY (OrderID, ProductID);
This script adds a composite primary key constraint PK_OrderDetails to the OrderID and ProductID columns of the OrderDetails table.
Elevate Your Database Game with TrainingHub.io's Expert SQL Server Developer Program!
Using SQL Server Management Studio (SSMS)
- Creating a Table with a Primary Key:
- Open SSMS and connect to the SQL Server instance.
- In Object Explorer, right-click the database, then click "New Table".
- Add columns, specify data types, and set the primary key by right-clicking the column and selecting "Set Primary Key".
- Save the table by clicking the save icon or pressing Ctrl+S.
- Adding a Primary 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 the primary key and select "Set Primary Key".
- Save the changes.
Managing Primary Keys
Dropping a Primary Key Constraint:
ALTER TABLE Employees
DROP CONSTRAINT PK_Employees;
This removes the primary key constraint PK_Employees from the Employees table.
Handling Primary Key Violations: If you try to insert a duplicate value into a primary key column, SQL Server will raise an error:
INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate, Salary)
VALUES (1, 'John', 'Doe', '2024-01-01', 50000.00);
-- Attempting to insert another row with the same EmployeeID
INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate, Salary)
VALUES (1, 'Jane', 'Smith', '2024-02-01', 60000.00);
The second insert statement will fail with a primary key violation error.
Get ahead in your career with Data Analyst courses in Toronto that emphasize real-world projects and case studies.
Benefits of Using Primary Keys
- Data Integrity: Ensures no duplicate rows and enforces uniqueness.
- Efficient Data Retrieval: Indexes created on primary keys improve query performance.
- Relationship Management: Essential for creating relationships between tables (foreign keys).
Example of Primary Key with Foreign Key
Creating Related Tables:
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName NVARCHAR(50)
);
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
HireDate DATE,
Salary DECIMAL(10, 2),
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) -- Foreign key relationship
);
In this example, the DepartmentID column in the Employees table references the primary key DepartmentID in the Departments table, establishing a foreign key relationship.
Creating Primary Key for a Temporary Table
Temporary tables are created in the tempdb database and can have primary keys like regular tables.
1. Creating a Temporary Table with a Primary Key:
- Single Column Primary Key:
CREATE TABLE #TempEmployees (
EmployeeID INT PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
HireDate DATE,
Salary DECIMAL(10, 2)
);
- Composite Primary Key:
CREATE TABLE #TempOrders (
OrderID INT,
ProductID INT,
Quantity INT,
PRIMARY KEY (OrderID, ProductID)
);
2. Adding Primary Key to an Existing Temporary Table:
- Single Column Primary Key:
CREATE TABLE #TempEmployees (
EmployeeID INT,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
HireDate DATE,
Salary DECIMAL(10, 2)
);
ALTER TABLE #TempEmployees
ADD CONSTRAINT PK_TempEmployees PRIMARY KEY (EmployeeID);
- Composite Primary Key:
CREATE TABLE #TempOrders (
OrderID INT,
ProductID INT,
Quantity INT
);
ALTER TABLE #TempOrders
ADD CONSTRAINT PK_TempOrders PRIMARY KEY (OrderID, ProductID);
Creating Primary Key for a View
Views in SQL Server do not support primary keys directly because views are virtual tables and do not store data physically. However, you can create an indexed view which can have unique
clustered indexes that act similarly to primary keys.
Creating an Indexed View with a Unique Clustered Index:
CREATE VIEW SalesSummary
WITH SCHEMABINDING
AS
SELECT StoreID, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY StoreID;
CREATE UNIQUE CLUSTERED INDEX IX_SalesSummary ON SalesSummary (StoreID);
Ready to Become a SQL Server Pro? Enroll in Our SQL Server Developer Course Now! TrainingHub.io offers a wide range of IT online courses in Canada to help you stay ahead in the tech industry.
Suggested blogs
Recent blogs
