Create Table

Tables are the core structure in a relational database.

A good CREATE TABLE statement gives you clean names, clear data intent, and guardrails that prevent bad data later.

Core Concepts

Anatomy of CREATE TABLE

flowchart LR
  A[CREATE TABLE table_name] --> B[Column definitions]
  B --> C[Optional constraints]
  C --> D[Stored in sqlite_schema]

Part	Example	Purpose
Table name	students	Logical container
Column definition	name TEXT	Data field + type affinity
Constraint	NOT NULL, PRIMARY KEY	Enforce rules

Minimal Table

-- Smallest useful table shape.
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT
);

Expected output
Statement executes successfully (no result rows).

Safer Table

-- Better defaults for application data quality.
CREATE TABLE students (
  id         INTEGER PRIMARY KEY,
  full_name  TEXT    NOT NULL,
  email      TEXT    UNIQUE,
  created_at TEXT    DEFAULT CURRENT_TIMESTAMP
);

Expected output
Statement executes successfully (no result rows).

Code Examples

-- Create a practical table for this course.
CREATE TABLE courses (
  course_id   INTEGER PRIMARY KEY,
  title       TEXT    NOT NULL,
  level       TEXT    DEFAULT 'beginner',
  published   INTEGER DEFAULT 0
);

Expected output
Table courses is created successfully.

-- Inspect the exact SQL stored for this table.
.schema courses

Expected output
A CREATE TABLE courses (...) statement appears.

SQLite-Specific Nuances

SQLite Nuance

SQLite does not require a separate CREATE DATABASE step.

You create tables directly inside the currently opened database file.

Common Pitfalls / Best Practices

Pitfall

Creating tables with vague column names like data or value.

Ambiguous names make queries harder to understand and maintain.

Best Practice

Design table names as plural nouns (users, orders) and use explicit column names (created_at, email).

Quick Challenge

Create a table named instructors with:

• instructor_id as integer primary key
• name as required text
• active defaulting to 1

View Solution

CREATE TABLE instructors (
  instructor_id INTEGER PRIMARY KEY,
  name          TEXT NOT NULL,
  active        INTEGER DEFAULT 1
);

Expected output
Table is created with one primary key, one required text field, and one defaulted flag.