db-constraints
Installation
SKILL.md
db-constraints (M5)
Constraints are invariants the database guarantees no matter which app, script, or migration writes the row. Without them, "this is always true" is a hope, not a fact. This module is design-axis (Constraints category). It applies to engines that enforce declarative constraints; document stores route to the Validación-schema category instead.
What it checks
- Missing NOT NULL: columns that are semantically required (FKs to mandatory parents,
email,status, timestamps) left nullable, allowing partial/garbage rows. - Missing CHECK: domain rules not enforced —
quantity >= 0,price >= 0,status IN (...),start_date <= end_date,email ~ '@'. The app "validates" it; the DB does not. - Missing UNIQUE: natural keys (email, username, slug, external_id) with no UNIQUE constraint, permitting duplicates that corrupt joins and auth.
- Case-insensitive uniqueness (emails): a plain
UNIQUEon a case-varying column (e.g.email) still admitsFoo@x.comandfoo@x.comas distinct rows — duplicate identities. Name the remedy: acitextcolumn type, or aUNIQUEexpression index onlower(col)(e.g.CREATE UNIQUE INDEX ON users (lower(email))), so case-folded values collide. - Over-nullable UNIQUE trap: a UNIQUE constraint over nullable columns where the engine treats each NULL as distinct, so duplicates slip through (e.g.
UNIQUE(user_id, deleted_at)for soft-delete uniqueness fails whendeleted_atis NULL). Recommend a partial/filtered unique index orNULLS NOT DISTINCT. - Constraint declared NOT VALID / NOCHECK and never validated.