# NOT NULL constraints on required fields

- **Pattern:** `ab-000900` (`database-design-operations.schema-design.not-null-constraints`)
- **Severity:** high
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000900
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000900)

## Why it matters

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.

## Severity rationale

High because nullable required fields permit invalid data to enter the database from any write path, including migration scripts and background jobs that bypass application validation.

## Remediation

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.

```sql
-- 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;
```

```prisma
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.

## Detection

- **ID:** `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:

  ```sql
  -- 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
  // 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.

---

## External references

- iso-25010 functional-suitability.functional-correctness

Taxons: data-integrity

HTML version: https://auditbuffet.com/patterns/ab-000900