MSDN Documentation

Primary Keys

A primary key is a column or a set of columns that uniquely identifies each row in a database table. It is a fundamental concept in relational database design, ensuring data integrity and enabling efficient data retrieval.

Why are Primary Keys Important?

• Uniqueness: Guarantees that no two rows in a table are identical.
• Data Integrity: Prevents duplicate records, which can lead to inconsistencies and errors.
• Efficient Retrieval: Allows for fast and precise access to specific rows using the primary key value.
• Relationships: Serves as the target for foreign keys, establishing links between tables.

Characteristics of a Primary Key

• Unique: Every value in the primary key column(s) must be unique.
• Non-NULL: A primary key column cannot contain NULL values. Each row must have a defined primary key.
• Stable: Ideally, a primary key value should not change once assigned to a row. Changing primary keys can be complex and impact related data.
• Minimal: If a primary key consists of multiple columns (a composite key), it should be minimal, meaning no subset of the columns would still uniquely identify the row.

Types of Primary Keys

Primary keys can be implemented in a few ways:

1. Single-Column Primary Key: A single column is designated as the primary key. This is the most common scenario.

   Example: An EmployeeID column in an Employees table.

2. Composite Primary Key: Two or more columns are combined to form the primary key. This is used when a single column is not sufficient to guarantee uniqueness.

   Example: In a table that links students to courses (e.g., Enrollments), the combination of StudentID and CourseID might form the primary key.

3. Surrogate Primary Key: A column that has no business meaning but is assigned a unique value (often an auto-incrementing integer) solely to serve as the primary key. This is often preferred for its simplicity and stability.

   Example: An CustomerID column that is an auto-generated number.

4. Natural Primary Key: A column that naturally exists in the data and has unique values, such as an email address or a product code. While seemingly convenient, these can sometimes change or be invalid (e.g., an employee leaves and their email address is deactivated), making them less ideal than surrogate keys.

Defining a Primary Key in SQL

You can define a primary key when creating a table or alter an existing table to add one.

Creating a Table with a Primary Key

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE
);

Creating a Table with a Composite Primary Key

CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

Adding a Primary Key to an Existing Table

ALTER TABLE Products
ADD CONSTRAINT PK_Products PRIMARY KEY (ProductID);

Constraints and Best Practices

• Always define a primary key for every table.
• Choose a primary key that is stable and unlikely to change.
• For new tables, consider using surrogate keys (e.g., auto-incrementing integers) for simplicity and performance.
• Understand the implications of composite keys on performance and query writing.
• Ensure that any columns used in a primary key are indexed automatically by the database system for fast lookups.

Understanding and correctly implementing primary keys is crucial for building robust and efficient relational databases.