Understand and identify suitable primary keys

📚 Databases – Primary Keys

What is a Primary Key?

A primary key is a column (or set of columns) that uniquely identifies each row in a table. Think of it as a student’s unique ID card – no two students can have the same card number. The key must always have a value (non‑null) and never repeat.

Key Properties (?? )

  • Uniqueness: ∀ a, b ∈ Table, a.key = b.key ⇒ a = b
  • Non‑null: key ≠ NULL
  • Minimal: no subset of the key can still be unique
  • Stable: the value rarely changes

Choosing a Primary Key – Step‑by‑Step

  1. List all candidate columns that might uniquely identify a row.
  2. Check each candidate for uniqueness and non‑null constraints.
  3. Prefer a single column over a composite key unless necessary.
  4. Make sure the key is stable – it won’t change often.
  5. Use surrogate keys (auto‑increment numbers) if natural keys are messy.

Example: Student Table

StudentID FirstName LastName Email
1001 Alice Smith alice@example.com
1002 Bob Jones bob@example.com

Here, StudentID is the primary key. It’s a single, auto‑generated number that satisfies all key properties.

Good vs. Bad Key Candidates

Candidate Uniqueness Non‑Null Stability Result
Email Often unique, but not guaranteed Can be NULL Users may change email ❌ Not ideal
StudentID (auto‑increment) Guaranteed unique Never NULL Doesn’t change ?? Ideal

Quick Quiz 🎯

  1. Which of the following could be a good primary key? (Choose all that apply)
    • Student’s full name
    • Student’s email address
    • Auto‑increment student number
    • Student’s date of birth
  2. Why is a surrogate key often preferred over a natural key?

Answers: 1) Auto‑increment student number. 2) Surrogate keys are stable, simple, and guaranteed unique, whereas natural keys can change or be duplicated.

Revision

Log in to practice.

1 views 0 suggestions