Foreign keys enforce referential integrity between related tables

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.

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.

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

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.

• ID: database-design-operations.schema-design.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 — 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[]
  }
  

  -- 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)