Creating and Altering Tables

So far we've been querying existing tables. Now let's learn how to define the structure of your database — called DDL (Data Definition Language).

CREATE TABLE

CREATE TABLE table_name (
  column_name data_type constraints,
  column_name data_type constraints,
  ...
);

Common Data Types

Type	Use Case	Examples
INTEGER / INT	Whole numbers	id, age, quantity
BIGINT	Large integers	Large IDs
DECIMAL(p, s)	Exact decimals	price DECIMAL(10,2)
REAL / FLOAT	Approximate decimals	latitude, longitude
TEXT	Variable-length text	description, notes
VARCHAR(n)	Text with max length	name VARCHAR(100)
BOOLEAN	True/false	is_active, is_verified
DATE	Date only	birth_date
TIMESTAMP	Date and time	created_at
UUID	Universally unique ID	PostgreSQL UUIDs

Constraints

Constraints enforce rules on your data:

CREATE TABLE products (
  id         INTEGER       PRIMARY KEY,      -- unique identifier, never null
  name       VARCHAR(200)  NOT NULL,         -- required
  price      DECIMAL(10,2) NOT NULL
                           CHECK (price > 0), -- must be positive
  category   VARCHAR(50)   NOT NULL,
  sku        VARCHAR(50)   UNIQUE,           -- must be unique (or null)
  created_at TIMESTAMP     DEFAULT NOW(),    -- auto-filled
  updated_at TIMESTAMP     DEFAULT NOW()
);

Primary Keys

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

-- Integer primary key (auto-incrementing)
CREATE TABLE items (
  id SERIAL PRIMARY KEY,  -- PostgreSQL: auto-increment
  name TEXT NOT NULL
);

-- SQLite
CREATE TABLE items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL
);

-- UUID primary key (PostgreSQL)
CREATE TABLE items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL
);

Foreign Keys

Link rows across tables:

CREATE TABLE orders (
  id         INTEGER PRIMARY KEY,
  user_id    INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity   INTEGER NOT NULL CHECK (quantity > 0),
  order_date TIMESTAMP DEFAULT NOW(),

  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

ON DELETE CASCADE — if the parent (user) is deleted, child rows (orders) are also deleted. ON DELETE RESTRICT — prevents deletion of a parent if children exist.

Composite Primary Keys

Some tables use multiple columns as their primary key:

CREATE TABLE user_course_enrollments (
  user_id   INTEGER REFERENCES users(id),
  course_id INTEGER REFERENCES courses(id),
  enrolled_at TIMESTAMP DEFAULT NOW(),