Entity Integrity Rules

Define the strict rules governing primary key uniqueness and non-nullability.

In this lesson, you will learn how the "Entity Integrity Rule" acts as a fundamental safeguard to ensure that every record in your database remains uniquely identifiable.

The Foundation of Identity

In the world of relational databases, an "entity" is essentially an object or concept—like a customer, a product, or an order. To manage these entities effectively, we must be able to point to one, and only one, specific row in a table. The Entity Integrity Rule is the ironclad law that governs this. It consists of two strict requirements for your Primary Key:

Uniqueness: No two rows can share the same primary key value.
Non-nullability: The primary key cannot be NULL.

Why these rules matter

Think of a primary key like a social security number or a fingerprint. If a database allowed a primary key to be NULL, you would have a "ghost record" that exists in the system but cannot be referenced or retrieved specifically. If it allowed duplicates, the database wouldn't know which row you meant when you asked to update or delete a specific entry.

Consider this table definition:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FullName VARCHAR(100),
    Department VARCHAR(50)
);

When you define EmployeeID as the PRIMARY KEY, the database engine automatically enforces the Entity Integrity Rule for you. If you attempt to run the following commands, the database will reject them:

Trying to insert a NULL: INSERT INTO Employees (EmployeeID, FullName) VALUES (NULL, 'John Doe'); — This fails because a primary key must have a value.
Trying to insert a duplicate: INSERT INTO Employees (EmployeeID, FullName) VALUES (101, 'Jane Smith'); (if 101 already exists) — This fails because the ID must be unique.

By enforcing these constraints, the database guarantees that every single row is a distinct, addressable entity. This provides the stability required for all your subsequent operations, such as linking tables or performing updates, without the risk of accidental data ambiguity.

Key Takeaway: The Entity Integrity Rule mandates that a primary key must be both unique and non-null to ensure every row in a table can be distinctly identified.