Databases / Keys

DATABASES · 12 MIN READ

Primary keys and foreign keys

Keys give a row an identity and let one table refer to a valid row in another table.

Learning objectives

  • Identify a primary key.
  • Explain a foreign key relationship.

Identity and reference

SQL
CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  department_id INTEGER REFERENCES departments(id)
);

departments.id uniquely identifies one department row. students.department_id is a foreign key: it refers to a department ID. The database can enforce referential integrity so that an enrollment does not point to a department that does not exist.

Common mistakes

  • A key should be stable enough for its intended identity; a display name can change.
  • Foreign-key behavior on delete or update is a deliberate schema decision, not a default to ignore.

References