Keys and integrity constraints

Why it matters

Keys are how a database knows which row is which and how rows in different tables refer to one another. The kinds of keys are a guaranteed DBMS exam question, and choosing them is a practical decision with long consequences: a natural key that turns out not to be unique, a missing foreign key that lets orphan rows accumulate, or a cascade that deletes far more than intended. Constraints are the cheapest bug prevention available, because the database enforces them for every writer.

The idea

A key is a set of attributes whose values identify each tuple uniquely. The family of keys differs in how minimal they are and which one is chosen.

Natural versus surrogate keys

A natural key comes from the data (email, ISBN, national ID). A surrogate key is an identifier the system invents (an auto-increment integer or a UUID) with no meaning outside the database. Natural keys change (people change emails), turn out not to be unique (shared phone numbers), and can be sensitive (national IDs). Most production schemas use a surrogate primary key and enforce the natural candidate keys with UNIQUE constraints, getting stable identity and real-world uniqueness at once.

Foreign keys and referential integrity

A foreign key says that a value in one table must exist as a key in another. The DBMS then refuses to insert an enrolment for a student who does not exist, and must decide what happens when a referenced row is deleted or its key updated.

Cascades are powerful and dangerous: a single delete can fan out through several levels of tables. Many teams prefer soft deletes or explicit application-level cleanup for anything with business value.

The other integrity constraints

1. 1

   Domain constraints

   Each value must belong to its attribute's domain: the type (INTEGER, DATE) plus a CHECK such as 'year BETWEEN 1 AND 4'.
2. 2

   NOT NULL

   The attribute must always have a value.
3. 3

   UNIQUE

   No two rows may share the value; NULLs are usually allowed and not considered equal to each other.
4. 4

   Entity integrity

   The primary key is UNIQUE and NOT NULL, so every row is identifiable.
5. 5

   Referential integrity

   Every foreign key value matches an existing key or is NULL.
6. 6

   General assertions and triggers

   Rules spanning rows or tables ('total enrolment cannot exceed room capacity') need triggers or application logic, since most databases do not support CREATE ASSERTION.

Trade-offs

Constraints cost a little on every write (unique and foreign key checks need indexes and lookups) and make bulk loads and some migrations more involved, in exchange for data that cannot violate the rules no matter which program writes it. Surrogate keys add a column and a join for lookups by natural key, in exchange for stable identity. Cascading actions save code and risk accidental mass deletion.

In an interview

Define the hierarchy precisely, then show judgement: "Candidate keys are minimal super keys; I pick a surrogate primary key, enforce email and national ID as alternate keys with UNIQUE, and use foreign keys with RESTRICT by default and CASCADE only for owned children like order lines." Mentioning that a foreign key column usually needs its own index, for joins and for the parent-delete check, earns a nod.