prefer-constrained-status · SQL

Why

A CHECK that rules out combinations of multiple required booleans exposes a shared state contract. A constrained status column can represent the permitted states explicitly and avoid invalid flag combinations.

Fix

Review whether these dependent booleans represent one lifecycle. Retain an authoritative Boolean when one bit determines the rest; use one constrained status for a multiway lifecycle. Preserve every valid combination, independent field, external contract and application transition.

Examples

Before — flagged A CHECK makes two required flags dependent

CREATE TABLE job (
  requested BOOLEAN NOT NULL,
  confirmed BOOLEAN NOT NULL,
  CHECK (
    NOT confirmed
    OR requested
  )
);

After — preferred Unrelated flags retain their independent values

CREATE TABLE job (
  enabled BOOLEAN NOT NULL,
  archived BOOLEAN NOT NULL
);

Before — flagged Multiple CHECKs jointly restrict three lifecycle observations

CREATE TABLE job (
  requested BOOLEAN NOT NULL,
  confirmed BOOLEAN NOT NULL,
  archived BOOLEAN NOT NULL,
  CHECK (
    NOT confirmed
    OR requested
  ),
  CHECK (
    NOT archived
    OR confirmed
  )
);

After — preferred A reviewed status preserves all four outcomes allowed by the implication chain

CREATE TABLE job (
  status TEXT NOT NULL CHECK (
    status IN ('new', 'requested', 'confirmed', 'archived')
  )
);

Before — flagged A tautological flag does not join the dependent lifecycle factor

CREATE TABLE job (
  requested BOOLEAN NOT NULL,
  confirmed BOOLEAN NOT NULL,
  muted BOOLEAN NOT NULL,
  CHECK (
    (
      NOT confirmed
      OR requested
    )
    AND (
      muted
      OR NOT muted
    )
  )
);

After — preferred A reviewed status models three outcomes while muted remains independent

CREATE TABLE job (
  status TEXT NOT NULL CHECK (status IN ('new', 'requested', 'confirmed')),
  muted BOOLEAN NOT NULL
);