# Foreign keys enforce referential integrity between related tables

- **Pattern:** `ab-000898` (`database-design-operations.schema-design.foreign-keys-present`)
- **Severity:** critical
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000898
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000898)

## Why it matters

Without foreign key constraints, the database allows orphan rows — comments that reference deleted posts, orders that reference nonexistent users, line items whose product was removed. Application code must then paper over these inconsistencies with defensive null checks, producing unpredictable behavior in edge cases. Missing `ON DELETE` clauses mean the database default (`NO ACTION`) silently blocks parent deletes or, in some configurations, leaves orphans. ISO 25010 functional-correctness requires that referential constraints are enforced at the storage layer, not assumed from application logic that can be bypassed by direct SQL access, background jobs, or migration scripts.

## Severity rationale

Critical because orphan rows corrupt business-critical data relationships and can only be detected after the fact, often requiring expensive reconciliation queries.

## Remediation

Add `@relation` with explicit `onDelete` in Prisma, or `FOREIGN KEY ... REFERENCES ... ON DELETE` in raw SQL, for every column that logically points to another table.

```prisma
model Comment {
  id     String @id @default(uuid())
  postId String
  post   Post   @relation(fields: [postId], references: [id], onDelete: Cascade)
}
```

```sql
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id)
  REFERENCES users(id)
  ON DELETE RESTRICT;
```

Choose `CASCADE` for owned data (comments belong to a post), `SET NULL` for optional references, and `RESTRICT` for financial or audit records where parent deletion must be blocked.

## Detection

- **ID:** `foreign-keys-present`
- **Severity:** `critical`
- **What to look for:** Count every column that is named with an `_id` suffix or that stores a reference to another table (e.g., `userId`, `post_id`, `order_id`, `organization_id`). For each such column, classify it as "has FK constraint" or "missing FK constraint." In Prisma, look for `@relation` decorators. In raw SQL, look for `REFERENCES table_name(column)` and `FOREIGN KEY` syntax. In Drizzle, look for `.references(() => table.column)`. In TypeORM, look for `@ManyToOne`, `@OneToMany`, `@ManyToMany` decorators. Also check whether `ON DELETE` behavior is explicitly defined — CASCADE, SET NULL, or RESTRICT/NO ACTION. Missing ON DELETE behavior means accidental orphan records if parent rows are deleted.
- **Pass criteria:** All columns that logically reference another table have FK constraints declared — at least 1 ON DELETE clause per FK. ON DELETE behavior is explicitly defined for every FK (not left to database default). Prisma `@relation` is present for all relationships. Raw SQL uses `FOREIGN KEY ... REFERENCES ... ON DELETE`. Report even on pass: "X FK columns found, all with explicit ON DELETE behavior."
- **Cross-reference:** For query performance on FK columns, see the `indexes-on-queries` check in the Schema Design category — unindexed FK columns cause full table scans on JOINs.
- **Fail criteria:** Columns named `*_id` exist without FK constraints. Relationships tracked only at the application layer (e.g., `userId` field with no DB-level constraint). Any FK is missing its `ON DELETE` clause.
- **Skip (N/A) when:** Single-table application with no relationships between entities (extremely rare in real projects).
- **Detail on fail:** Specify which relationships are unenforced. Example: `"'comments' table has 'post_id' and 'user_id' columns with no FK constraints in schema.prisma — orphan comments possible."` or `"FK exists on 'orders.user_id' but ON DELETE behavior is undefined — Postgres will default to NO ACTION."`.
- **Remediation:** Add FK constraints with explicit ON DELETE behavior:

  ```prisma
  // Prisma — one-to-many relationship with cascade delete
  model Comment {
    id     String @id @default(uuid())
    postId String
    post   Post   @relation(fields: [postId], references: [id], onDelete: Cascade)
  }

  model Post {
    id       String    @id @default(uuid())
    comments Comment[]
  }
  ```

  ```sql
  -- Raw SQL — FK with explicit ON DELETE
  ALTER TABLE comments
    ADD CONSTRAINT fk_comments_post
    FOREIGN KEY (post_id)
    REFERENCES posts(id)
    ON DELETE CASCADE;

  ALTER TABLE orders
    ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id)
    REFERENCES users(id)
    ON DELETE RESTRICT;  -- Prevent deleting users who have orders
  ```

  Choose ON DELETE behavior intentionally:
  - `CASCADE` — delete child rows when parent is deleted (good for owned data like comments)
  - `SET NULL` — nullify the FK column (good for optional relationships)
  - `RESTRICT`/`NO ACTION` — block parent deletion if children exist (good for financial records)

---

## External references

- iso-25010 functional-suitability.functional-correctness

Taxons: data-integrity

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