Entity Integrity

Definition

Entity integrity is the rule that every table's primary key value must be unique AND never NULL. It guarantees that every row represents one distinct, identifiable, real "entity" — you can never have a row whose identity is unknown or shared with another row.

How It Works

Declaring PRIMARY KEY in SQL directly implements entity integrity — both halves (uniqueness and NOT NULL) are enforced automatically the moment you write id INT PRIMARY KEY, with no extra keywords needed:

sql
CREATE TABLE students (
  id INT PRIMARY KEY,   -- entity integrity: id is both UNIQUE and NOT NULL, automatically
  name VARCHAR(100) NOT NULL
);

Without entity integrity, two serious problems become possible: (1) a row with id = NULL — a row that exists but has no identity, which the relational model has no coherent way to talk about ("which row do you mean?"); and (2) two rows both claiming id = 7 — an ambiguous reference, since any query or foreign key pointing at "the row with id 7" would no longer know which one is meant.

Edge Cases and Pitfalls

• Entity integrity applies specifically to the PRIMARY KEY — a UNIQUE alternate-key column is NOT subject to entity integrity's NOT-NULL half (as covered in 5.9, UNIQUE still permits NULLs).
• Entity integrity is a NECESSARY condition for a working relational database, but it is not SUFFICIENT on its own for overall data correctness — a row can perfectly satisfy entity integrity (a valid, unique, non-null id) while still containing wrong or nonsensical data in its other columns.
• Composite primary keys must satisfy entity integrity as a whole combination — no single component column of the composite key is allowed to be NULL either, since a NULL in any part of the key breaks the guarantee that the row's identity is fully known.

Key Takeaways

• Entity integrity = primary key values are always unique and never NULL.
• It's automatically enforced by declaring PRIMARY KEY — no extra syntax required.
• It guarantees every row has a well-defined, unambiguous identity, but says nothing about the correctness of the row's other data.