SQLite Create Table

A table is the fundamental structure in any relational database. It organizes data into rows and columns — like a spreadsheet, but with rules. The CREATE TABLE statement builds that structure before any data can be stored.

Table Structure Analogy

SPREADSHEET VIEW OF A "students" TABLE
───────────────────────────────────────────────────
Column →    id    │   name    │  age  │  email
            ──────┼───────────┼───────┼──────────────
Row 1  →    1     │  Alice    │  20   │  alice@x.com
Row 2  →    2     │  Bob      │  22   │  bob@x.com
Row 3  →    3     │  Carol    │  21   │  carol@x.com

Each column has a name and a data type.
Each row is one student's complete record.

Basic CREATE TABLE Syntax

CREATE TABLE table_name (
  column1_name  data_type  [constraints],
  column2_name  data_type  [constraints],
  ...
);

Your First Table

CREATE TABLE students (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  age   INTEGER,
  email TEXT
);

Reading This Statement

CREATE TABLE students    ← create a table named "students"
(
  id    INTEGER PRIMARY KEY  ← whole number, unique identifier
  name  TEXT NOT NULL        ← text, cannot be left empty
  age   INTEGER              ← whole number, optional
  email TEXT                 ← text, optional
);

IF NOT EXISTS — Safe Creation

Running CREATE TABLE on a table that already exists causes an error. Add IF NOT EXISTS to skip creation silently when the table is already there.

-- Without IF NOT EXISTS: ERROR if table exists
CREATE TABLE students (...);

-- With IF NOT EXISTS: safe to run multiple times
CREATE TABLE IF NOT EXISTS students (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  age   INTEGER,
  email TEXT
);

PRIMARY KEY Column

A PRIMARY KEY column uniquely identifies every row. No two rows can share the same primary key value. In SQLite, an INTEGER PRIMARY KEY column automatically numbers itself — starting from 1 and incrementing by 1 with each new row.

INSERT INTO students (name, age) VALUES ('Alice', 20);
INSERT INTO students (name, age) VALUES ('Bob',   22);

sqlite> SELECT * FROM students;
id │ name  │ age
───┼───────┼────
1  │ Alice │ 20
2  │ Bob   │ 22

id was assigned automatically ↑

Creating Multiple Tables

CREATE TABLE IF NOT EXISTS courses (
  id    INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  units INTEGER
);

CREATE TABLE IF NOT EXISTS enrollments (
  student_id INTEGER,
  course_id  INTEGER,
  grade      REAL
);

Table Naming Rules

VALID TABLE NAMES          INVALID TABLE NAMES
───────────────────────    ──────────────────────────────
students                   2students   (starts with number)
student_records            student records (has space)
StudentInfo                SELECT  (reserved SQL keyword)
my_table_2024              my-table (hyphen not allowed)

Table names are case-insensitive in SQLite. Students, STUDENTS, and students all refer to the same table.

Viewing Table Structure

-- See the CREATE TABLE statement
sqlite> .schema students
CREATE TABLE students (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  age   INTEGER,
  email TEXT
);

-- See column info
sqlite> PRAGMA table_info(students);
cid │ name  │ type    │ notnull │ dflt_value │ pk
────┼───────┼─────────┼─────────┼────────────┼────
0   │ id    │ INTEGER │ 0       │            │ 1
1   │ name  │ TEXT    │ 1       │            │ 0
2   │ age   │ INTEGER │ 0       │            │ 0
3   │ email │ TEXT    │ 0       │            │ 0

TEMPORARY Tables

A temporary table exists only for the current session. It disappears when you close the connection or the shell.

CREATE TEMPORARY TABLE temp_results (
  score INTEGER,
  label TEXT
);

-- Visible in this session:
sqlite> .tables
temp_results

-- Gone after .quit

Complete Example: Library System

CREATE TABLE IF NOT EXISTS authors (
  id      INTEGER PRIMARY KEY,
  name    TEXT NOT NULL,
  country TEXT
);

CREATE TABLE IF NOT EXISTS books (
  id          INTEGER PRIMARY KEY,
  title       TEXT NOT NULL,
  author_id   INTEGER,
  year        INTEGER,
  price       REAL DEFAULT 0.0
);

RELATIONSHIP DIAGRAM
───────────────────────────────────────────────
authors                     books
─────────────────           ─────────────────────
id  (PK) ◄──────────────── author_id
name                        id  (PK)
country                     title
                            year
                            price

Key Takeaways

• Use CREATE TABLE name (col type, ...) to define a table
• Add IF NOT EXISTS to prevent errors on duplicate creation
• An INTEGER PRIMARY KEY column auto-numbers rows starting from 1
• Table names cannot start with a digit or contain spaces
• Use PRAGMA table_info(name) to inspect column details