Schema Design
CREATE TABLE, data types, constraints, and normalization
Skills taught: sql.create-table, sql.data-types, sql.constraints, sql.foreign-keys, sql.alter-table
CREATE TABLE and data types
-- Create the users table
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
city TEXT,
joined_at TEXT NOT NULL DEFAULT (DATE('now'))
);
-- Create the products table
CREATE TABLE IF NOT EXISTS products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
category TEXT NOT NULL,
price REAL NOT NULL CHECK (price >= 0), -- no negative prices!
stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);
-- Create the orders table with FOREIGN KEY references
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
quantity INTEGER NOT NULL CHECK (quantity > 0),
total REAL NOT NULL,
ordered_at TEXT NOT NULL DEFAULT (DATETIME('now'))
);
ALTER TABLE and DROP TABLE
-- Add a column (SQLite supports ADD COLUMN)
ALTER TABLE users ADD COLUMN phone TEXT;
-- Rename a column (SQLite 3.25+)
ALTER TABLE users RENAME COLUMN phone TO phone_number;
-- SQLite does NOT support DROP COLUMN on older versions
-- PostgreSQL: ALTER TABLE users DROP COLUMN phone_number;
-- Drop a table
-- DROP TABLE IF EXISTS old_table;
-- See table structure
PRAGMA table_info(users);
-- See all tables
SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;
Frequently Asked Questions
What are the SQLite data types?
SQLite uses 5 storage classes: NULL, INTEGER (1-8 bytes), REAL (8-byte float), TEXT (UTF-8), and BLOB (raw binary). Unlike PostgreSQL which is strictly typed, SQLite stores values in the most efficient format based on 'type affinity'. This means TEXT '42' and INTEGER 42 can both live in the same column.
How do I enable foreign key enforcement in SQLite?
By default, SQLite parses FOREIGN KEY syntax but doesn't enforce it. Run PRAGMA foreign_keys = ON; at the start of every connection. This is a per-connection setting — you must set it each time you open the database.