SQLite Cheatsheet 03 — Schema and Indexes

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL CHECK(length(name) > 0),
    age INTEGER CHECK(age >= 0),
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
) STRICT;

CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX uniq_users_phone ON users(phone) WHERE phone IS NOT NULL;
CREATE INDEX idx_users_status_created ON users(status, created_at);

CREATE INDEX idx_active ON users(email) WHERE active = 1;

CREATE INDEX idx_lower_email ON users(lower(email));

SELECT * FROM users WHERE lower(email) = 'x';   -- uses index

CREATE INDEX idx_cover ON users(status, name, email);
SELECT status, name, email FROM users WHERE status = 'x';   -- no table lookup

PRAGMA foreign_keys = ON;     -- per connection

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title TEXT
);

ALTER TABLE users ADD COLUMN phone TEXT;
ALTER TABLE users RENAME COLUMN old TO new;
ALTER TABLE users RENAME TO users_new;
ALTER TABLE users DROP COLUMN bio;     -- 3.35+

CREATE VIEW active_users AS
SELECT * FROM users WHERE active = 1;

CREATE TRIGGER update_timestamp
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
END;

CREATE TABLE orders (
    items_json TEXT,
    total REAL GENERATED ALWAYS AS (json_extract(items_json, '$.total')) STORED
);

CREATE TABLE users (
    age INTEGER CHECK(age >= 0 AND age < 150),
    status TEXT CHECK(status IN ('active', 'inactive'))
);

CREATE TABLE kv (
    key TEXT PRIMARY KEY,
    value TEXT
) WITHOUT ROWID;

PRAGMA user_version;
PRAGMA user_version = 1;

ANALYZE;