How to create a table in SQL — CREATE TABLE with keys and foreign keys

You are defining a new table with a primary key, required columns and a foreign key to another table.

Every example runs against the cheatsheet's sample tables — departments(id, name), employees(id, name, dept_id, salary, manager_id, hired_on) and orders(id, emp_id, amount, ordered_on) — and was executed and verified against SQLite, so you can paste it in and get the same result.

CREATE TABLE projects (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  lead_id INTEGER REFERENCES employees(id)
);
-- Primary keys, NOT NULL and foreign keys belong in the schema, not in application code
-- SQLite note: foreign keys are parsed but NOT enforced unless you run PRAGMA foreign_keys = ON

Good to know

Dialect: INTEGER PRIMARY KEY is SQLite's auto-incrementing row id; MySQL writes INT AUTO_INCREMENT PRIMARY KEY; PostgreSQL writes INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY (or the older SERIAL).

SQL Cheatsheet one-page card — queries that actually work — The full one-page card — SQL Cheatsheet. Get the complete cheatsheet on GitHub