Tables and DDL

CREATE TABLE users(
  id         INTEGER PRIMARY KEY,        -- this IS the rowid, the fast path
  email      TEXT NOT NULL UNIQUE,
  name       TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;

SELECT rowid, * FROM users LIMIT 1;
SELECT max(rowid) FROM users;

CREATE TABLE a(id INTEGER PRIMARY KEY, v TEXT);
INSERT INTO a(v) VALUES('x'),('y');
DELETE FROM a WHERE id=2;
INSERT INTO a(v) VALUES('z');
SELECT id, v FROM a;      -- 1|x  and  2|z   <- id 2 was reused

CREATE TABLE b(id INTEGER PRIMARY KEY AUTOINCREMENT, v TEXT);
INSERT INTO b(v) VALUES('x'),('y');
DELETE FROM b WHERE id=2;
INSERT INTO b(v) VALUES('z');
SELECT id, v FROM b;      -- 1|x  and  3|z   <- never reused
SELECT * FROM sqlite_sequence;   -- b|3

CREATE TABLE kv(
  k TEXT PRIMARY KEY,
  v TEXT NOT NULL
) STRICT, WITHOUT ROWID;

CREATE TABLE posts(
  id         INTEGER PRIMARY KEY,
  author_id  INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  slug       TEXT NOT NULL COLLATE NOCASE,
  title      TEXT NOT NULL CHECK(length(trim(title)) > 0),
  status     TEXT NOT NULL DEFAULT 'draft'
               CHECK(status IN ('draft','published','archived')),
  views      INTEGER NOT NULL DEFAULT 0 CHECK(views >= 0),
  published_at TEXT
               CHECK(published_at IS NULL OR published_at LIKE '____-__-__%'),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(author_id, slug),
  CHECK(status <> 'published' OR published_at IS NOT NULL)
) STRICT;

CREATE TABLE e(email TEXT UNIQUE COLLATE NOCASE) STRICT;
INSERT INTO e VALUES('Ada@Example.com');
INSERT INTO e VALUES('ada@example.com');
-- Error: UNIQUE constraint failed: e.email

CREATE TABLE items(
  id      INTEGER PRIMARY KEY,
  price   INTEGER NOT NULL,          -- cents
  qty     INTEGER NOT NULL,
  total   INTEGER AS (price * qty),                       -- VIRTUAL, the default
  payload TEXT NOT NULL,
  sku     TEXT AS (payload ->> '$.sku') STORED,
  slug    TEXT GENERATED ALWAYS AS (lower(payload ->> '$.name')) VIRTUAL
) STRICT;

PRAGMA foreign_keys;        -- 0 on a fresh connection
PRAGMA foreign_keys = ON;   -- per connection, not persisted in the file
PRAGMA foreign_keys;        -- 1

PRAGMA foreign_keys = OFF;
INSERT INTO posts(author_id, slug, title) VALUES(999, 'x', 'T');  -- accepted
PRAGMA foreign_keys = ON;
INSERT INTO posts(author_id, slug, title) VALUES(999, 'y', 'T');
-- Error: FOREIGN KEY constraint failed

PRAGMA foreign_key_check;             -- list every violating row, whole database
PRAGMA foreign_key_check(posts);      -- one table
PRAGMA foreign_key_list('posts');     -- the declared keys of a table
PRAGMA defer_foreign_keys = ON;       -- defer to COMMIT for this transaction only

CREATE INDEX idx_posts_author ON posts(author_id, created_at DESC);
CREATE UNIQUE INDEX idx_posts_slug ON posts(slug);
CREATE INDEX idx_posts_live ON posts(created_at)
  WHERE status = 'published';                    -- partial index, 3.8.0+
CREATE INDEX idx_users_lower ON users(lower(email));   -- expression index, 3.9.0+
CREATE INDEX idx_posts_cover ON posts(author_id, status, title);  -- covering
DROP INDEX IF EXISTS idx_posts_slug;
REINDEX posts;                                   -- rebuild, after a collation change

EXPLAIN QUERY PLAN
SELECT id FROM posts WHERE status='published' ORDER BY created_at;
-- SEARCH posts USING INDEX idx_posts_live (>?)

CREATE VIEW live_posts AS
  SELECT p.id, p.title, u.email
  FROM posts p JOIN users u ON u.id = p.author_id
  WHERE p.status = 'published';

CREATE TRIGGER posts_touch AFTER UPDATE ON posts
FOR EACH ROW WHEN old.title <> new.title
BEGIN
  UPDATE posts SET updated_at = datetime('now') WHERE id = old.id;
END;

CREATE TRIGGER posts_no_delete BEFORE DELETE ON posts
BEGIN
  SELECT RAISE(ABORT, 'posts are archived, not deleted');
END;

CREATE TRIGGER live_posts_ins INSTEAD OF INSERT ON live_posts
BEGIN
  INSERT INTO posts(author_id, slug, title, status)
  VALUES(1, new.title, new.title, 'published');
END;

ALTER TABLE posts RENAME TO articles;                 -- 3.25+ also fixes references
ALTER TABLE posts RENAME COLUMN slug TO url_slug;     -- 3.25+
ALTER TABLE posts ADD COLUMN pinned INTEGER DEFAULT 0;
ALTER TABLE posts DROP COLUMN pinned;                 -- 3.35+

ALTER TABLE t ADD COLUMN y TEXT NOT NULL;
-- Error: Cannot add a NOT NULL column with default value NULL
--   (only on a non-empty table, and only when there is no DEFAULT)
ALTER TABLE t ADD COLUMN y TEXT DEFAULT (datetime('now'));
-- Error: Cannot add a column with non-constant default
ALTER TABLE t ADD COLUMN y INTEGER PRIMARY KEY;
-- Error: Cannot add a PRIMARY KEY column
ALTER TABLE t ADD COLUMN y TEXT UNIQUE;
-- Error: Cannot add a UNIQUE column
ALTER TABLE t ADD COLUMN y TEXT AS (x || '!') STORED;
-- Error: cannot add a STORED column
ALTER TABLE t ADD COLUMN y TEXT AS (x || '!');        -- VIRTUAL is allowed

-- 1. If foreign keys are on, turn them off
PRAGMA foreign_keys = OFF;
-- 2. Start a transaction
BEGIN;
-- 3. Save the CREATE statements of every index, trigger, and view on the table
SELECT type, sql FROM sqlite_schema WHERE tbl_name = 'posts';
-- 4. Create the new table under a temporary name, with the schema you want
CREATE TABLE posts_new(
  id INTEGER PRIMARY KEY,
  author_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  slug TEXT NOT NULL,
  title TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft'
) STRICT;
-- 5. Copy the data across, transforming as needed
INSERT INTO posts_new(id, author_id, slug, title, status)
  SELECT id, author_id, slug, title, coalesce(status,'draft') FROM posts;
-- 6. Drop the old table
DROP TABLE posts;
-- 7. Rename the new table into place
ALTER TABLE posts_new RENAME TO posts;
-- 8. Recreate the indexes, triggers, and views from step 3
CREATE INDEX idx_posts_author ON posts(author_id);
-- 9. Recreate any views that referenced the table
-- 10. If foreign keys were on, check them
PRAGMA foreign_key_check;
-- 11. Commit
COMMIT;
-- 12. Turn foreign keys back on
PRAGMA foreign_keys = ON;