Understanding Referential Actions
1. Using Cascade Delete
| Aspect | Detail |
|---|---|
| Effect | Deletes child rows when parent deleted |
| Syntax | onDelete: Cascade |
| Use case | Comments under a post, audit trail per resource |
2. Using Set Null
| Requirement | Detail |
|---|---|
| FK must be optional | e.g., authorId Int? |
| Behavior | FK nulled when parent deleted/updated |
3. Using Set Default
| Requirement | Detail |
|---|---|
| FK default set | authorId Int @default(0) |
| Behavior | FK reset to default when parent deleted |
4. Using Restrict
| Behavior | Detail |
|---|---|
| Immediate | Prevents delete if dependents exist |
| Throws | P2003 foreign key error |
5. Using No Action
| Aspect | Detail |
|---|---|
| Default | Used when no action specified (depends on DB) |
| PG | Deferred check at end of transaction |
| MongoDB | Only NoAction or Cascade with prisma mode |
6. Configuring Update Actions
| Option | Effect |
|---|---|
onUpdate: Cascade | Propagate PK change to FKs |
onUpdate: Restrict | Block PK update if FK exists |
| When to use | Rarely; immutable PKs preferred |
7. Combining Delete and Update Actions
author User @relation(fields:[authorId], references:[id],
onDelete: Cascade, onUpdate: Restrict)| Pair | Effect |
|---|---|
| Cascade + Restrict | Delete children but block PK rename |
| SetNull + Cascade | Update propagates, deletes detach |
8. Understanding Database-Specific Behavior
| Engine | Caveat |
|---|---|
| SQL Server | Multiple cascade paths to same table forbidden |
| PG | Supports all actions natively |
| MongoDB | Emulated in Prisma client (relationMode = "prisma") |
9. Handling Circular References
Warning: Two-way cascading deletes between models can create infinite cycles in some engines. Choose one direction as Cascade and the other as Restrict or NoAction.
| Risk | Mitigation |
|---|---|
| Cycle | Use SetNull on one side |
| Detection | prisma migrate dev emits error |
10. Validating Referential Integrity
| Mode | Behavior |
|---|---|
relationMode = "foreignKeys" | Native DB constraints (default) |
relationMode = "prisma" | Prisma emulates (for PlanetScale/serverless) |
| Trade-off | App-level mode requires explicit indexes |