NOT NULL constraints on required fields

Nullable required fields shift data integrity guarantees from the database to application code. When users.email or orders.status allows NULL, any code path — a background job, a direct SQL migration, a third-party integration — can insert invalid rows that the application is not equipped to handle. The downstream effect is defensive ?. and null-coalescing operators scattered throughout the codebase, and sporadic runtime errors when those checks are missed. ISO 25010 functional-correctness treats this as a schema design failure: fields that have no valid null state should have that stated in the schema, not enforced by convention.

Add NOT NULL to required columns in a migration. In Prisma, remove the ? suffix from required fields. When retrofitting onto a column that has existing NULLs, backfill before constraining.

-- Backfill first, then constrain
UPDATE orders SET status = 'pending' WHERE status IS NULL;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

ALTER TABLE users
  ALTER COLUMN email SET NOT NULL,
  ALTER COLUMN created_at SET NOT NULL;

model User {
  email     String   // NOT NULL — no ? suffix
  createdAt DateTime @default(now())
  bio       String?  // nullable — intentional
}

Legitimately optional fields (bio, avatar_url, middle_name) keep their ?; every other field should have a documented reason for being nullable.

• ID: database-design-operations.schema-design.not-null-constraints

• Severity: high

• What to look for: Enumerate every column in the schema and classify it as "must always have a value" or "legitimately optional." For each nullable column, quote the column definition to confirm whether it lacks NOT NULL. Fields that must always have a value include: email, password_hash, created_at, updated_at, status fields, tenant/organization IDs, and any column the application logic treats as required. In Prisma, fields without ? are non-null by default — this is correct. Fields marked with ? (optional) should have a documented business reason for being nullable. In raw SQL, check for NOT NULL on required columns. Watch for schemas where everything is nullable by default (a sign of schema-after-the-fact generation), or where timestamps, status fields, and identity columns allow NULL.

• Pass criteria: Required business fields have NOT NULL constraints — no more than 20% of total columns should be nullable without clear business justification. Timestamps (created_at, updated_at) are non-null. Status/type fields are non-null. FK columns that are required relationships are non-null. Nullable columns are intentional and documented or obviously optional (e.g., bio, avatar_url, middle_name).

• Do NOT pass when: All or most columns are nullable by default with no schema design intent — this indicates auto-generated schemas without review.

• Fail criteria: Critical fields (email, status, created_at) allow NULL. All columns nullable by default with no schema design intent. Application code has defensive null checks for fields that should be database-guaranteed non-null.

• Skip (N/A) when: N/A — always applicable for any project with a database.

• Detail on fail: Specify which required fields allow NULL. Example: "'users.email' is nullable in the schema — email should always be required." or "'orders.status' allows NULL — an order without a status is meaningless and defensive null checks in code suggest this is a mistake." or "All 8 columns in 'profiles' table are nullable including 'user_id'.".

• Remediation: Add NOT NULL to required columns:

  -- Add NOT NULL to required columns in a migration
  ALTER TABLE users
    ALTER COLUMN email SET NOT NULL,
    ALTER COLUMN created_at SET NOT NULL;
  
  ALTER TABLE orders
    ALTER COLUMN status SET NOT NULL,
    ALTER COLUMN user_id SET NOT NULL;
  

  // Prisma — required fields have no ? suffix
  model User {
    id           String   @id @default(uuid())
    email        String   // NOT NULL (no ?)
    createdAt    DateTime @default(now()) // NOT NULL
    bio          String?  // nullable — intentional, optional field
    avatarUrl    String?  // nullable — intentional, optional field
  }
  

  When retrofitting NOT NULL onto existing columns with NULL values, first backfill the NULLs: UPDATE users SET email = 'unknown@placeholder.invalid' WHERE email IS NULL; then apply the constraint.