DBMS

The Relational Model

Keys and Constraints

Chapter 1 established that a relation is a set, so it cannot contain duplicate tuples. That fact alone creates the need for keys.

JrCodex·7 min read

Jr Codex DBMS Notes

Level: Beginner Prerequisites: Chapter 1: Relations, Tuples and Domains Time to complete: ~20 minutes

Table of Contents

  1. Why Keys Are Forced On You
  2. The Key Hierarchy
  3. Choosing a Primary Key
  4. Foreign Keys
  5. The Integrity Rules
  6. Other Constraints
  7. Summary & Next Steps

1. Why Keys Are Forced On You

Chapter 1 established that a relation is a set, so it cannot contain duplicate tuples. That fact alone creates the need for keys.

The Reasoning
─────────────────────────────────────────
  If tuples must be distinct, then SOMETHING about
  each tuple must distinguish it.

  And if row ORDER is meaningless, you cannot
  identify a row by position either.

  So the only way to refer to a specific row is by
  its VALUES. A key is the set of attributes whose
  values do that job.

  Keys are not an add-on. They are what makes a row
  addressable at all.
─────────────────────────────────────────

2. The Key Hierarchy

Four terms, nested inside each other.

The Nesting
─────────────────────────────────────────
  SUPER KEY
    Any attribute set that uniquely identifies a
    tuple. May contain redundant attributes.
      {id}, {id, name}, {id, name, city}, {email}

  CANDIDATE KEY
    A super key with NO redundant attribute —
    remove any attribute and uniqueness is lost.
    ("minimal", not "smallest")
      {id}, {email}

  PRIMARY KEY
    The ONE candidate key you choose as the
    official identifier.
      {id}

  ALTERNATE KEY
    The candidate keys you did not choose.
      {email}
─────────────────────────────────────────

Worked Example
─────────────────────────────────────────
  students(id, email, name, city, marks)
  where id and email are each unique.

  SUPER KEYS      {id}, {email}, {id,name},
                  {email,city}, {id,email}, ...
                  (many)

  CANDIDATE KEYS  {id}, {email}
                  — {id,name} is NOT, because
                    dropping name keeps uniqueness

  PRIMARY KEY     {id}          (chosen)
  ALTERNATE KEY   {email}       (enforce with UNIQUE)
─────────────────────────────────────────

Composite Keys
─────────────────────────────────────────
  A key may be several attributes together:

    enrollments(student_id, course_id, grade)
    PRIMARY KEY (student_id, course_id)

  Neither alone is unique — a student takes many
  courses, a course has many students — but the
  pair is. This is the standard shape for a
  many-to-many relationship (Module 4).
─────────────────────────────────────────

3. Choosing a Primary Key

Natural vs Surrogate
─────────────────────────────────────────
  NATURAL KEY     an attribute that already has
                  business meaning
                  email, ISBN, national id

  SURROGATE KEY   a meaningless value invented for
                  the purpose
                  auto-increment integer, UUID
─────────────────────────────────────────

Why Surrogate Usually Wins
─────────────────────────────────────────
  NATURAL KEYS CHANGE. People change email
  addresses and surnames. A primary key that
  changes must be updated in every table that
  references it — a cascading rewrite, and a
  concurrency problem.

  NATURAL KEYS ARE BIGGER. Every foreign key and
  every index entry stores a copy. A 4-byte integer
  beats a 60-character email everywhere it appears.

  NATURAL KEYS TURN OUT NOT TO BE UNIQUE. The
  supposedly-unique identifier acquires duplicates,
  nulls, or an edge case nobody anticipated. This
  happens more often than people expect.
─────────────────────────────────────────

The Rule
─────────────────────────────────────────
  Use a SURROGATE key as the primary key.
  Enforce the natural key with UNIQUE alongside it.

  You get a stable, compact identifier AND the
  business rule, and neither compromises the other.
─────────────────────────────────────────

CREATE TABLE students (
    id    INTEGER PRIMARY KEY,          -- surrogate: stable, small
    email TEXT NOT NULL UNIQUE,         -- natural key, still enforced
    name  TEXT NOT NULL
);

4. Foreign Keys

A foreign key is an attribute in one relation whose values must appear as a primary key in another. It is how relationships are expressed without pointers.

CREATE TABLE courses (
    id    INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);
 
CREATE TABLE enrollments (
    student_id INTEGER NOT NULL REFERENCES students(id),
    course_id  INTEGER NOT NULL REFERENCES courses(id),
    grade      TEXT,
    PRIMARY KEY (student_id, course_id)
);
What It Guarantees
─────────────────────────────────────────
  You cannot enrol a student that does not exist.
  You cannot enrol in a course that does not exist.

  These are impossible STATES, not merely
  discouraged ones — the database refuses them for
  every writer, including the one written at 2am by
  someone who did not read the docs.
─────────────────────────────────────────

Referential Actions
─────────────────────────────────────────
  What happens to enrollments when a referenced
  student is deleted?

  RESTRICT / NO ACTION   refuse the delete while
                         references exist. The safe
                         default.

  CASCADE                delete the enrollments too.
                         Correct when the child
                         CANNOT exist without the
                         parent.

  SET NULL               keep the row, null the
                         reference. Requires the
                         column to be nullable.

  SET DEFAULT            point it at a default row.
─────────────────────────────────────────

-- CASCADE is right here: an enrollment is meaningless without its student.
student_id INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE
 
-- RESTRICT is right here: never silently delete a customer's order history.
customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE RESTRICT
Choose Deliberately
─────────────────────────────────────────
  CASCADE is convenient and occasionally
  catastrophic — a chain of cascades can delete far
  more than intended from one statement.

  Ask: "if the parent disappears, is the child
  meaningless, or is it a record I need?" Meaningless
  ──► CASCADE. A record ──► RESTRICT.
─────────────────────────────────────────

5. The Integrity Rules

Two rules define what a valid relational database is.

ENTITY INTEGRITY
─────────────────────────────────────────
  No part of a primary key may be NULL.

  WHY: a primary key identifies a tuple. NULL means
  "unknown" (Chapter 1), and an unknown identifier
  identifies nothing. Worse, NULL = NULL is UNKNOWN,
  so uniqueness could not even be checked.

REFERENTIAL INTEGRITY
─────────────────────────────────────────
  Every foreign key value must either match an
  existing primary key, or be entirely NULL.

  WHY: a reference to a row that does not exist is
  a dangling pointer — the exact failure the
  relational model was designed to eliminate
  (Module 1, Chapter 2).

  NULL is permitted because "no relationship yet"
  is a legitimate state — an order not yet assigned
  to a courier.
─────────────────────────────────────────

6. Other Constraints

The Full Toolkit
─────────────────────────────────────────
  NOT NULL     the value must be present
  UNIQUE       no two rows share this value
               (unlike PRIMARY KEY, allows NULL)
  CHECK        an arbitrary boolean per row
  DEFAULT      value used when none supplied
  DOMAIN       a named, reusable constrained type
─────────────────────────────────────────

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    email       TEXT    NOT NULL UNIQUE,
    quantity    INTEGER NOT NULL DEFAULT 1 CHECK (quantity > 0),
    status      TEXT    NOT NULL DEFAULT 'pending'
                        CHECK (status IN ('pending','shipped','delivered','cancelled')),
    placed_at   TEXT    NOT NULL DEFAULT (datetime('now')),
    shipped_at  TEXT,
    CHECK (shipped_at IS NULL OR shipped_at >= placed_at)      -- ACROSS columns
);
The Argument for Doing This in the Schema
─────────────────────────────────────────
  Every one of these rules could live in
  application code. Putting them in the schema
  differs in three ways:

  UNIVERSAL   they hold for the API, the admin
              script, the migration, and the manual
              fix at 2am

  PERMANENT   they hold for data written before the
              current code existed

  DECLARED    the schema documents what valid data
              IS, in one readable place

  Application validation is still worth having, for
  better error messages. It is not a substitute.
─────────────────────────────────────────

7. Summary & Next Steps

Key Takeaways

  • Because relations are unordered sets of distinct tuples, values are the only way to address a row — which makes keys structural rather than optional.
  • Super key, candidate key, primary key and alternate key nest inside one another; a candidate key is minimal, meaning no attribute can be dropped.
  • Prefer a surrogate primary key and enforce the natural key with UNIQUE: natural keys change, are larger, and turn out not to be unique.
  • Entity integrity forbids NULL in a primary key; referential integrity forbids dangling foreign keys, and the referential action should follow whether the child is meaningless without its parent.

Concept Check

  1. Why is {id, name} a super key but not a candidate key when id is already unique?
  2. Give two independent reasons to prefer a surrogate primary key over an email address.
  3. An orders table references customers. Which referential action would you choose on delete, and what is the argument for it?

Next Chapter

→ Chapter 3: Relational Algebra

Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index | Back to DBMS Index