SR

CREATE TABLE

Define a table — columns, types, and the constraints that keep data clean

Beginner 14 minDDLCREATE TABLEconstraints

Defining a Table

Up to now every query has read from tables that already exist. CREATE TABLE is how those tables come into being. You name the table, list the columns with their data types, and optionally attach constraints that enforce rules at write time. Here's what goes into a good table definition:

Columns and types — Each column has a name and a type. Common types: INTEGER, REAL (floating-point), TEXT, BLOB. SQLite is dynamically typed but still respects these declarations. Postgres and MySQL enforce them strictly.
PRIMARY KEY — Uniquely identifies each row. Automatically indexed and implicitly NOT NULL in standard SQL. Every well-designed table should have one. id INTEGER PRIMARY KEY in SQLite auto-increments when omitted from INSERT. SQLite quirk: for compatibility with code written in 2001 (back when SQLite had a NULL-in-PK bug), non-INTEGER PRIMARY KEY columns can still hold NULL unless you also write NOT NULL explicitly. INTEGER PRIMARY KEY is the only PK type that auto-rejects NULLs in SQLite. Postgres, MySQL, and SQL Server all reject NULL in any PK column.
NOT NULL — Refuses INSERTs that don't supply a value for this column. Use for any column where a missing value would make the row meaningless (a customer without a name, an order without a total).
DEFAULT — Supplies a value when the INSERT doesn't provide one. Great for timestamps (DEFAULT CURRENT_TIMESTAMP), status fields (DEFAULT 'pending'), and boolean flags (DEFAULT 0).
CHECK — Row-level validation: salary REAL CHECK(salary >= 0) refuses negative salaries. rating INTEGER CHECK(rating BETWEEN 1 AND 5) restricts to 1–5. Pushes data-quality rules into the database where they can't be bypassed.
FOREIGN KEY — Declares that a column references another table's primary key. FOREIGN KEY (customer_id) REFERENCES customers(id) prevents inserting orders for customers that don't exist. In SQLite, FK enforcement is off by default — turn it on with PRAGMA foreign_keys = ON.
UNIQUE — Like PRIMARY KEY but can apply to any column (or combination). email TEXT UNIQUE prevents two rows from sharing the same email. Creates an implicit index.
Knowledge Canvas

How CREATE TABLE Works

Declare columns, types, and constraints

  • Each column has a name and a type (INTEGER, REAL, TEXT, BLOB)
  • PRIMARY KEY uniquely identifies each row and is auto-indexed
  • Constraints (NOT NULL, UNIQUE, CHECK, FK) run on every INSERT/UPDATE
  • CREATE TABLE IF NOT EXISTS is safe to re-run

Common Constraints

What each one does

  • PRIMARY KEY — unique + not null + indexed
  • NOT NULL — refuses missing values
  • UNIQUE — no two rows share this value
  • DEFAULT expr — fills in when INSERT omits
  • CHECK(cond) — row-level validation
  • FOREIGN KEY (col) REFERENCES t(id) — referential integrity

Schema Traps

Common mistakes

  • No primary key → can't reliably UPDATE/DELETE specific rows
  • Storing numbers/dates as TEXT → broken sort and arithmetic
  • SQLite FKs are OFF by default — turn on with PRAGMA foreign_keys = ON
  • ALTER COLUMN type is not supported in SQLite — plan ahead

Type Picking Guide

Choosing column types

  • INTEGER for counts, IDs, flags
  • REAL for prices (NUMERIC(10,2) in Postgres)
  • TEXT for strings and ISO dates
  • Timestamps: TEXT in SQLite, TIMESTAMP in Postgres
Syntax
Syntax Template
1CREATE TABLE table_name (
2 col_a INTEGER PRIMARY KEY,
3 col_b TEXT NOT NULL,
4 col_c TEXT UNIQUE,
5 col_d TEXT DEFAULT 'pending',
6 col_e REAL CHECK(col_e >= 0),
7 col_f INTEGER,
8 FOREIGN KEY (col_f) REFERENCES other_table(id)
9);
10
11-- Safer: only create if it doesn't already exist
12CREATE TABLE IF NOT EXISTS t (...);
PRIMARY KEYUnique row identifier; automatically indexed
NOT NULLRefuses rows missing this value
DEFAULT 'x'Fills in a value when INSERT omits this column
CHECK(expr)Row-level validation — refuses rows where expr is false
FOREIGN KEYEnforces that this column references an existing row in another table
CREATE TABLE IF NOT EXISTSSafe re-run — no error if table already exists

Worked Example

Define an employees table with constraints, then insert two rows and verify the schema.

SQL
1CREATE TABLE IF NOT EXISTS employees (
2 id INTEGER PRIMARY KEY,
3 name TEXT NOT NULL,
4 email TEXT UNIQUE,
5 department TEXT DEFAULT 'Unassigned',
6 salary REAL CHECK(salary >= 0),
7 manager_id INTEGER,
8 FOREIGN KEY (manager_id) REFERENCES employees(id)
9);
10
11INSERT INTO employees (id, name, email, department, salary) VALUES
12 (1, 'Aarav', '[email protected]', 'Engineering', 95000),
13 (2, 'Sara', '[email protected]', 'Design', 78000);
14
15SELECT id, name, email, department, salary FROM employees ORDER BY id;
The CREATE TABLE statement declares six columns with constraints: id is the primary key, name cannot be null, email must be unique, department defaults to "Unassigned" if omitted, salary cannot be negative, and manager_id references another row in the same employees table (a self-referential foreign key — how org hierarchies are modeled). After INSERTing two rows, the SELECT verifies both stored correctly. Run each statement one at a time in the Practice Lab.
Output
2 rows
idnameemaildepartmentsalary
1Aarav[email protected]Engineering95000
2Sara[email protected]Design78000
Common Mistakes
❌

No primary key

SQL
1CREATE TABLE notes (text TEXT, created_at TEXT);

A table without a primary key has no way to uniquely identify a specific row. You cannot reliably UPDATE or DELETE "just this row" — you can only match on content, which is ambiguous if two rows have identical values.

Add id INTEGER PRIMARY KEY — every production table should have a primary key.

❌

Storing numbers in TEXT columns

SQL
1CREATE TABLE prices (amount TEXT);

Storing numeric values as TEXT breaks sorting ("9" sorts after "10" alphabetically) and arithmetic. Similarly, dates stored as TEXT work in SQLite with ISO format but are fragile in other databases.

Use REAL for money, INTEGER for counts, TEXT only for actual text. In Postgres, use NUMERIC(10,2) for currency to avoid floating-point rounding.

Key Concepts

1A table is defined by columns, their types, and constraints
2PRIMARY KEY = unique + not null + automatically indexed
3NOT NULL rejects missing values; DEFAULT supplies one if omitted
4FOREIGN KEY links to another table and enforces referential integrity

Pro Tip

Constraints are the database's way of refusing bad data. A well-constrained schema prevents entire classes of bugs that application code would otherwise have to catch.

When to Use

Every new application, feature, or analysis pipeline starts with a table definition. Good constraints now prevent data-quality problems forever.

Previous

Working with NULL

Next

INSERT Statement