Entity Integrity

What is Entity Integrity?

Entity integrity is a fundamental rule in relational databases that ensures each entity (row) in a table can be uniquely identified. It’s one of the core integrity rules defined by E.F. Codd in the relational model and serves as the foundation for reliable data storage and retrieval.

The Primary Key Rule

Entity integrity is enforced through the primary key constraint, which states:

Every table must have a primary key, and the column(s) that make up the primary key must contain unique, non-null values for each row in the table.

This rule guarantees that:

Each row in a table represents a distinct entity
Each entity can be uniquely identified and accessed
No duplicate entities exist in the table

Implementing Entity Integrity

Primary Key Definition

A primary key can be:

A single column (simple primary key)
Multiple columns combined (composite primary key)

Example of a Simple Primary Key:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    BirthDate DATE
);

Example of a Composite Primary Key:

CREATE TABLE CourseEnrollment (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    Grade CHAR(2),
    PRIMARY KEY (StudentID, CourseID)
);

Primary Key Requirements

For entity integrity to be maintained, a primary key must have these properties:

Uniqueness: No two rows can have the same primary key value
Non-null: Primary key columns cannot contain NULL values
Immutability: Primary key values should rarely or never change
Minimality: The key should contain only the columns necessary for uniqueness

How DBMS Enforces Entity Integrity

Database management systems enforce entity integrity through several mechanisms:

Unique Indexes: Automatically creates indexes to enforce uniqueness
NOT NULL Constraints: Prevents NULL values in primary key columns
Validation Checks: Verifies that new/updated values don’t duplicate existing primary keys
Error Messages: Generates errors when entity integrity would be violated

Natural vs. Surrogate Keys

When implementing entity integrity, you can choose between two approaches:

Natural Keys

Natural keys use existing attributes of an entity that naturally identify it.

Characteristics:

Based on real-world attributes
Have business meaning
May be composite (multiple columns)
Can sometimes change over time

Examples:

Social Security Number for a person
ISBN for a book
Product code for inventory

When to use:

When a natural identifier already exists
When the identifier has business meaning
When the identifier is stable

Surrogate Keys

Surrogate keys are artificial identifiers created solely for the purpose of uniquely identifying rows.

Characteristics:

No business meaning
Usually auto-incremented numbers or GUIDs
Simple (single column)
Never change

Examples:

Auto-incremented CustomerID
System-generated UUID
Sequential order numbers

When to use:

When no natural key exists
When natural keys might change
When natural keys are complex or long
When performance is critical