Understanding Relational Database Keys

Keys are fundamental to relational database design. They are used to uniquely identify records within a table and to establish relationships between tables. Understanding different types of keys is crucial for building robust and efficient databases.

Primary Keys

A primary key is a column or a set of columns that uniquely identifies each record (row) in a table. It enforces entity integrity, meaning that no two rows in a table can have the same primary key value, and the primary key column(s) cannot contain NULL values.

Uniqueness: Every value in the primary key must be unique.
Non-NULL: Primary key columns cannot contain NULL values.
Single Primary Key: A table can have only one primary key.

Example: Employees Table

In an `Employees` table, `EmployeeID` would typically be the primary key.


CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100) UNIQUE
);

Foreign Keys

A foreign key is a column or a set of columns in one table that refers to the primary key in another table. It establishes a link between two tables and enforces referential integrity. Referential integrity ensures that the relationship between tables remains consistent: if a value is present in the foreign key column, it must also exist in the referenced primary key column of the parent table.

Referential Integrity: Ensures that relationships between tables are valid.
NULL Values Allowed: Foreign key columns can sometimes allow NULL values if the relationship is optional.
Multiple Foreign Keys: A table can have multiple foreign keys referencing different tables.

Example: Orders Table

An `Orders` table might have a `CustomerID` foreign key referencing the `CustomerID` primary key in a `Customers` table.


CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Unique Keys

A unique key constraint ensures that all values in a column or a set of columns are unique across all rows in a table. Unlike a primary key, a unique key constraint can allow one NULL value (depending on the specific database system).

Uniqueness: All values must be unique.
Nullable: Can often contain one NULL value.
Multiple Unique Keys: A table can have multiple unique keys.

Example: Users Table

In a `Users` table, `Email` might be a unique key because each user should have a distinct email address.


CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50) UNIQUE,
    Email VARCHAR(100) UNIQUE,
    PasswordHash VARCHAR(255)
);

Composite Keys

A composite key is a key that consists of two or more columns. These columns, when combined, uniquely identify each record in a table. Composite keys are often used when a single column is not sufficient to guarantee uniqueness.

Combined Uniqueness: The combination of values in the specified columns must be unique.
Part of Primary/Unique Key: Can be used as a primary key or a unique key.

Example: Order Details Table

In an `OrderDetails` table, a combination of `OrderID` and `ProductID` might form a composite primary key, assuming a product can only appear once per order.


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

Surrogate vs. Natural Keys

Keys can also be categorized by their origin:

Surrogate Key: A key that has no business meaning and is generated artificially by the database, often as an auto-incrementing integer. It's typically used as a primary key for simplicity and guaranteed uniqueness. Example: `EmployeeID`.
Natural Key: A key that is derived from the actual attributes of the entity, possessing inherent business meaning. Example: `EmailAddress` in a `Users` table, if it's guaranteed to be unique and non-null.

Choosing the Right Key

The choice of keys depends on the specific requirements of your database. Generally, using a simple, auto-generated surrogate key (like an integer `ID`) as the primary key is recommended for performance and ease of management. Natural keys can be used as unique constraints or as part of composite keys.