Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 36 ยท Relations

Self-Relations, Named Relations and Referential Actions

Model trees and followers with self-relations, name relations when two models are linked twice, and control deletes with onDelete and onUpdate referential actions.

This lesson covers the last relation topics you need before building real schemas: a model that relates to itself (categories with subcategories, employees with managers, users who follow users), models linked by more than one relation, and referential actions, the rules that decide what happens to related rows when you delete or change a record.

Self-relations: a tree

A category may have a parent category and many children. Both sides are the same model:

PRISMA
model Category {
  id       Int        @id @default(autoincrement())
  name     String
  parent   Category?  @relation("CategoryTree", fields: [parentId], references: [id])
  parentId Int?
  children Category[] @relation("CategoryTree")
}
  • parentId Int? is optional, so top-level categories have no parent.
  • Because both sides use the same model, the relation needs a name, "CategoryTree", so Prisma knows which parent goes with which children.
TypeScript
await prisma.category.create({
  data: {
    name: "Electronics",
    children: {
      create: [{ name: "Phones" }, { name: "Laptops" }],
    },
  },
});

const tree = await prisma.category.findMany({
  where: { parentId: null },
  include: { children: { include: { children: true } } },   // two levels
});

Prisma does not offer recursive queries ("all descendants") through the client. For arbitrary depth, load the rows and build the tree in JavaScript, store a materialized path, or use a recursive CTE with $queryRaw (chapter 6).

Self-relations: many-to-many (followers)

PRISMA
model User {
  id        Int    @id @default(autoincrement())
  name      String
  followers User[] @relation("Follows")
  following User[] @relation("Follows")
}

Both fields are lists with the same relation name, so Prisma creates an implicit join table _Follows. followers and following are the two directions of the same link. When you need data on the link (the date someone followed), make an explicit Follow model with followerId and followingId, like the enrollment example.

Two relations between the same models

Suppose a post has an author and a reviewer, both users. Two relations connect User and Post, so each needs a name:

PRISMA
model User {
  id               Int    @id @default(autoincrement())
  name             String
  authoredPosts    Post[] @relation("Author")
  reviewedPosts    Post[] @relation("Reviewer")
}

model Post {
  id         Int   @id @default(autoincrement())
  title      String
  author     User  @relation("Author", fields: [authorId], references: [id])
  authorId   Int
  reviewer   User? @relation("Reviewer", fields: [reviewerId], references: [id])
  reviewerId Int?
}

Without the names Prisma reports an ambiguous relation and refuses to generate the client. Name relations after the role ("Author", "Reviewer"), not after the models.

Try it: building a tree from self-relation rows

Because the client returns flat rows, you often turn parent ids into a nested tree yourself. This stand-in does that with a single query's worth of rows.

Node.js Stand-in: from parentId to a tree

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Referential actions

A foreign key creates a promise: a post's authorId always points at a real user. What happens when you delete the user, or change the user's id? Referential actions answer that, and you set them on the owning side of the relation:

PRISMA
model Post {
  id       Int  @id @default(autoincrement())
  author   User @relation(fields: [authorId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  authorId Int
}
Action Effect when the referenced row is deleted or updated
Cascade delete (or update) the referencing rows too
Restrict refuse the operation while referencing rows exist
NoAction like Restrict, checked at the end of the statement (database dependent)
SetNull set the foreign key to null (the field must be optional)
SetDefault set the foreign key to its default value

Defaults when you write nothing: onDelete: Restrict for required relations (SetNull for optional ones) and onUpdate: Cascade. Choose deliberately:

  • Cascade for children that mean nothing alone: comments of a deleted post, items of a deleted order.
  • Restrict for things you must not lose by accident: a customer who still has invoices.
  • SetNull for weak links: a post's reviewerId when the reviewer leaves.

Cascade deletes are powerful and silent. Deleting one User can delete thousands of posts and comments. Use it only where the children truly belong to the parent, and prefer soft deletes for data you might need back.

Try it: the three behaviours in real SQL

This runs the three most useful actions on SQLite, so you can see exactly what each does to the child rows.

Node.js Cascade, Restrict and SetNull

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Referential actions without database foreign keys

Some platforms (PlanetScale-style databases, some sharded setups) do not allow foreign key constraints. Prisma can emulate relations then, with relationMode = "prisma" in the datasource:

PRISMA
datasource db {
  provider     = "mysql"
  relationMode = "prisma"
}

Prisma Client then performs the checks and cascades itself with extra queries. You also have to add @@index on every foreign key yourself. It works, but it is slower and cannot protect rows from other programs, so use real foreign keys whenever your database allows them.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does a self-relation like Category.parent / Category.children need a relation name?

    Both sides point at the same model, so the shared name (for example 'CategoryTree') pairs the two fields.
  2. 2. A post has an author and a reviewer, both users. What do you need?

    Multiple relations between the same two models must have distinct names to be unambiguous.
  3. 3. Which referential action should you choose for comments of a deleted post?

    Comments mean nothing without their post, so deleting the post should delete them (Cascade).
  4. 4. What does onDelete: SetNull require of the foreign key field?

    The database sets the column to NULL, so the column and relation field must be optional.
  5. 5. Which action prevents deleting a user who still has invoices?

    Restrict refuses the delete while referencing rows exist.

Summary

  • Self-relations model trees (parent/children) and graphs (followers); give the relation a name.
  • Use named relations when two models are linked more than once, and name them by role.
  • onDelete and onUpdate choose Cascade, Restrict, NoAction, SetNull or SetDefault; pick them per relationship.
  • relationMode = "prisma" emulates foreign keys when the database has none, at a cost.

Next: The Prisma Migrate Workflow