Database Design: Keys and Constraints

In relational database design, keys and constraints are fundamental concepts that ensure data integrity, consistency, and efficient data retrieval. They define the rules and relationships within your database, preventing invalid data from being entered and guiding how data can be accessed and manipulated.

What are Keys?

Keys are special columns or sets of columns that uniquely identify rows within a table or establish relationships between tables.

Primary Keys

A Primary Key (PK) is a column or a set of columns that uniquely identifies each record in a table. It must contain unique values and cannot contain NULL values. Each table should ideally have a primary key.

Uniqueness: Every value in the primary key column(s) must be unique.
Non-Nullability: Primary key columns cannot contain NULL values.
One per Table: A table can have only one primary key.

Example: Identifying Customers

Consider a Customers table:

CustomerID (PK)	FirstName	LastName	Email
101	Alice	Smith	alice.smith@example.com
102	Bob	Johnson	bob.j@example.com
103	Charlie	Williams	c.williams@example.com

In this example, CustomerID is the primary key. Each customer has a unique ID, and no customer can have a missing ID.

Foreign Keys

A Foreign Key (FK) 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, enforcing referential integrity.

Referential Integrity: Ensures that a value in the foreign key column must exist in the referenced primary key column of the other table, or be NULL (if allowed).
Relationship Enforcement: Prevents orphaned records (e.g., an order without a valid customer).

Example: Linking Orders to Customers

Consider an Orders table linked to the Customers table:

OrderID (PK)	CustomerID (FK)	OrderDate	TotalAmount
5001	101	2023-10-26	150.75
5002	102	2023-10-26	75.00
5003	101	2023-10-27	220.50

Here, CustomerID in the Orders table is a foreign key referencing the CustomerID (primary key) in the Customers table. This ensures that every order is associated with a valid customer.

Candidate Keys

A Candidate Key is any column or set of columns that could serve as a primary key. It has unique values and can be NULL (though a primary key cannot).

Superkey

A Superkey is a set of attributes that uniquely identifies a tuple in a relation. A primary key is a minimal superkey.

What are Constraints?

Constraints are rules defined on table columns to enforce data validity and integrity. They restrict the type of data that can be inserted or updated in a database.

NOT NULL Constraint

Ensures that a column cannot have a NULL value. This is often applied to primary key columns and other essential fields.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255) NOT NULL,
    Price DECIMAL(10, 2)
);

UNIQUE Constraint

Ensures that all values in a column (or a set of columns) are distinct. It allows NULL values, but only one NULL value is typically permitted.

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

CHECK Constraint

Restricts the range of values that can be placed in a column. It allows you to define a condition that must be true for the data.

Example: Ensuring Positive Stock

CREATE TABLE Inventory (
    ItemID INT PRIMARY KEY,
    ItemName VARCHAR(255),
    StockQuantity INT CHECK (StockQuantity >= 0)
);

This constraint ensures that StockQuantity can never be negative.

DEFAULT Constraint

Assigns a default value to a column when no value is specified during an INSERT operation.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE DEFAULT GETDATE(),
    Status VARCHAR(50) DEFAULT 'Pending'
);

Referential Integrity

Referential integrity, enforced by foreign keys, ensures that relationships between tables remain consistent. When a foreign key constraint is defined, operations like deleting or updating records in the parent table can be handled in several ways:

CASCADE: If a record in the parent table is deleted or updated, corresponding records in the child table are also deleted or updated.
SET NULL: If a record in the parent table is deleted or updated, the foreign key columns in the child table are set to NULL.
SET DEFAULT: If a record in the parent table is deleted or updated, the foreign key columns in the child table are set to their default values.
NO ACTION / RESTRICT: The operation on the parent table is rejected if it would violate referential integrity.

Best Practice: Designing with primary keys and appropriate constraints from the outset is crucial for building robust, reliable, and maintainable database systems.

Understanding and implementing keys and constraints is a cornerstone of effective relational database management. They are the guardians of your data's quality and consistency.

MSDN Documentation