Creating Tables

Tables are the foundation of any SQLite database. This lesson covers the full CREATE TABLE syntax, column constraints, primary keys, foreign keys, and how to modify or remove tables.

Basic CREATE TABLE Syntax

CREATE TABLE table_name (
  column1  type  [constraints],
  column2  type  [constraints],
  ...
  [table_constraints]
);

A minimal example:

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

Primary Keys

Every table should have a primary key — a column (or set of columns) that uniquely identifies each row.

INTEGER PRIMARY KEY is special in SQLite: the column becomes an alias for SQLite's internal rowid, and its value is automatically assigned if you insert NULL or omit it:

CREATE TABLE articles (
  id         INTEGER PRIMARY KEY,
  title      TEXT NOT NULL,
  body       TEXT,
  created_at TEXT DEFAULT (datetime('now'))
);
-- id is auto-assigned on each INSERT
INSERT INTO articles (title, body) VALUES ('First Post', 'Hello world');

For composite primary keys, declare them as a table constraint:

CREATE TABLE enrollments (
  student_id INTEGER NOT NULL,
  course_id  INTEGER NOT NULL,
  enrolled_at TEXT DEFAULT (datetime('now')),
  PRIMARY KEY (student_id, course_id)
);

Column Constraints

Constraint	Effect
NOT NULL	Rejects NULL values
UNIQUE	Ensures no two rows share the same value
DEFAULT value	Fills the column if no value is supplied
CHECK (expr)	Rejects rows where the expression is false

CREATE TABLE products (
  id       INTEGER PRIMARY KEY,
  name     TEXT    NOT NULL,
  price    REAL    NOT NULL CHECK (price >= 0),
  stock    INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
  sku      TEXT    UNIQUE
);

Foreign Keys

SQLite supports foreign keys but requires you to enable them at runtime — they are disabled by default for backwards compatibility:

PRAGMA foreign_keys = ON;

CREATE TABLE categories (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE products (
  id          INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  category_id INTEGER NOT NULL REFERENCES categories(id) ON DELETE CASCADE
);

The REFERENCES clause declares the foreign key. ON DELETE CASCADE means that deleting a category automatically deletes all products in that category.

CREATE TABLE IF NOT EXISTS

To avoid an error when the table already exists: