Keys, Constraints and Honest Data

Every constraint you skip is a class of bad row you have agreed to accept.

Pick a primary key on purpose

CHAR(36) defaults of (UUID()) are the right call when ids travel between services, because a service can mint one without a round trip. Two costs worth knowing:

• Index locality. Random uuids scatter inserts across the B-tree instead of appending to it. For an append-heavy table you only ever read by time - the analytics event stream here - BIGINT AUTO_INCREMENT is the better trade.
• Storage. A CHAR(36) in utf8mb4 reserves 144 bytes, because MySQL budgets four bytes per character. A uuid only ever contains hex and hyphens, so every id column in this schema is declared CHARACTER SET ascii and takes
  1. Foreign key columns must repeat the same charset, or InnoDB refuses the constraint outright.

Both key styles appear in this schema on purpose. Compare analytics_events with catalog_courses.

Write the CHECK constraint

A published course must have a publication date. That is not a nice-to-have, it is what "published" means:

CONSTRAINT ck_courses_published_has_date
  CHECK (status <> 'published' OR published_at IS NOT NULL)

Two lines, and an entire family of "why is this course showing a blank date" bugs simply cannot happen. The application can forget. The database will not.

MySQL only started enforcing CHECK constraints in 8.0.16 - before that it parsed and ignored them, which is worse than not supporting them. Check your version before relying on one.

Emulating a partial unique index

Sometimes uniqueness only applies to a subset of rows. A learner may take a quiz many times, but only one attempt may be open at once. PostgreSQL writes that as a partial index. MySQL has none, so the trick is a generated column that is NULL for every other state, because a MySQL unique index permits duplicate NULLs:

open_attempt_key VARCHAR(80) GENERATED ALWAYS AS (
  CASE WHEN state = 'in_progress' THEN CONCAT(quiz_id, ':', user_id) END
) VIRTUAL,
UNIQUE KEY uq_one_open_attempt (open_attempt_key)

VIRTUAL rather than STORED for a specific reason: MySQL refuses a cascading foreign key on a column that a stored generated column reads, and quiz_id needs both.

Try to enforce that rule in application code and you will lose a race eventually. The index cannot lose it.

Reordering without deferrable constraints

Positions within a module are unique, so reordering lessons produces a transient duplicate. PostgreSQL solves this with DEFERRABLE INITIALLY DEFERRED, which checks at COMMIT. MySQL checks immediately and has no equivalent.

The workaround is to park every position in the negative range first, where it cannot collide with any target value, then write the final numbers:

UPDATE catalog_lessons SET position = -position WHERE module_id = ?;
-- now write 1..n

That is what catalog_reorder_lessons() does.

The one to remember

Nullable columns are a claim that the value is genuinely optional. Most nullable columns in a young schema are really "we had not decided yet". Decide.