Basics Updated November 2026

Primary and foreign keys

A primary key is the column (or columns) that uniquely identifies each row and is never NULL, such as customers.id. A foreign key is a column in another table, such as orders.customer_id, whose values must exist in that primary key, so orders for customers that do not exist are rejected. Joins connect the two.

Syntax

id INTEGER PRIMARY KEY | PRIMARY KEY (col1, col2) | customer_id INTEGER REFERENCES customers(id) [ON DELETE CASCADE]

Examples

CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);
CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept_id INTEGER REFERENCES departments(id)
);

Result: employees.dept_id must match an existing departments.id (or be NULL)

CREATE TABLE order_items (
  order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
  product_id INTEGER REFERENCES products(id),
  qty INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

Result: Composite primary key; deleting an order deletes its items automatically

INSERT INTO employees (name, dept_id) VALUES ('Ana', 99);

Result: Error: FOREIGN KEY constraint failed (no department 99)

In SQLite this only fails if PRAGMA foreign_keys = ON has been set; PostgreSQL always enforces it.

Common mistakes

Inserting a child row before the parent exists fails the foreign key check; insert parents first.
Deleting a parent that still has children raises a constraint error; delete the children, set ON DELETE CASCADE, or ON DELETE SET NULL.
Expecting SQLite to enforce foreign keys by default; it does not, run PRAGMA foreign_keys = ON per connection.

Practice it

The drills on Querydrill check your answer on live data, free, no account needed.

Start practicing

Questions

Can a table have more than one primary key?

No, exactly one, but it can span several columns (a composite key). Additional uniqueness rules are expressed with UNIQUE constraints.

Can a foreign key be NULL?

Yes unless the column is declared NOT NULL. A NULL foreign key means no related row, for example an employee without a manager.

CREATE TABLEHow to create a table with column types, NOT NULL, DEFAULT, UNIQUE, PRIMARY KEY and FOREIGN KEY in SQLite and PostgreSQL, plus CREATE TABLE AS SELECT.
INNER JOININNER JOIN returns rows that have a match in both tables. Syntax with ON, joining three tables, aliases, columns from both sides, and the common pitfalls.
LEFT JOINLEFT JOIN returns every row of the first table and matching rows from the second, or NULL. Find rows without a match, count with zeros, avoid the WHERE trap.
DELETEDELETE FROM table WHERE condition removes matching rows. Delete by id, by subquery, duplicates, everything in a table, and DELETE versus TRUNCATE.