Keys and Relationships

Overview

A primary key uniquely identifies each row in a table, and a foreign key is a column that points to a primary key in another table. Together they let you model relationships like "an order belongs to a user." This matters because keys keep references valid and prevent orphaned or duplicated data.

Syntax / Usage

Declare a PRIMARY KEY on the identifying column, then use REFERENCES to create a foreign key that links to it.

CREATE TABLE users (
  id    INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  id       INTEGER PRIMARY KEY,
  user_id  INTEGER NOT NULL REFERENCES users(id),
  total    NUMERIC(10, 2) NOT NULL
);

Examples

Joining two tables through the foreign key to combine related data:

SELECT users.email, orders.total
FROM orders
JOIN users ON users.id = orders.user_id;

A many-to-many relationship uses a join table with two foreign keys:

CREATE TABLE student_courses (
  student_id INTEGER REFERENCES students(id),
  course_id  INTEGER REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)
);

Common Mistakes

• Using a mutable value like an email as a primary key instead of a stable id
• Omitting foreign keys, which lets rows reference records that do not exist
• Forgetting that a foreign key column should usually be NOT NULL when the relationship is required
• Deleting a parent row without deciding what happens to children (use ON DELETE rules)
• Modeling many-to-many with duplicated columns instead of a join table

See Also

databases-relational-basics databases-normalization databases-indexes