One-to-One & One-to-Many Relations

Prisma Fundamentals

Chapter 7 · Relations I: One-to-One & One-to-Many

So far the blog's User and Post models have no connection: a post doesn't know who wrote it. Real data is full of these links — posts have authors, users have profiles, comments belong to posts. This chapter adds them, explains the two kinds of field every relation needs, and covers what happens to related rows when something is deleted.

Three Kinds of Relation

Kind	Example	Covered in
One-to-many	One user writes many posts; each post has one author	This chapter
One-to-one	One user has at most one profile; each profile belongs to one user	This chapter
Many-to-many	A post has many tags; a tag is on many posts	Chapter 8

One-to-Many: Users and Posts

model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  // ...other fields from Chapter 3
  posts Post[]                                  // relation field: a user's posts
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  // ...other fields from Chapter 3
  authorId Int                                  // relation scalar field: the foreign key
  author   User   @relation(fields: [authorId], references: [id])

  @@index([authorId])
}

Every relation involves two different kinds of field:

Field	Kind	In the database?	Purpose
`authorId`	Relation scalar field (foreign key)	Yes: a real column	Stores the ID of the related user
`author`	Relation field	No	Lets Prisma Client load or connect the related user
`posts`	Relation field (the other side)	No	Lets you reach a user's posts from the user

The @relation(fields: [authorId], references: [id]) attribute goes on the side that holds the foreign key. It says: "authorId in this table refers to id in the User table." Both sides of a relation must be declared, and prisma format will add a missing side for you.

Index your foreign keys

You'll often look up "all posts by this author," which searches the authorId column. MySQL adds an index for a foreign key automatically, but PostgreSQL doesn't, so add @@index([authorId]) to keep those lookups fast.

Required or optional?

As written, every post must have an author: authorId is Int and author is User. To allow posts with no author (for example, imported posts), make both optional:

  authorId Int?
  author   User?  @relation(fields: [authorId], references: [id])

Adding a required relation to a table with data

If your Post table already has rows, adding a required authorId leaves them without an author, so the migration can't be applied as it is (Chapter 4). In development, resetting is fine. With real data, add the column as optional, fill in an author for every existing post, then make it required in a second migration.

One-to-One: Users and Profiles

model User {
  id      Int      @id @default(autoincrement())
  profile Profile?                              // at most one profile
  posts   Post[]
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  avatar String?
  userId Int     @unique                      // @unique makes it one-to-one
  user   User    @relation(fields: [userId], references: [id])
}

The only structural difference from one-to-many is @unique on the foreign key. Without it, several profiles could point at the same user; with it, each user can have at most one. The user's side is Profile? because a user may not have created a profile yet.

Which side holds the foreign key?

Put it on the side that can't exist without the other. A profile makes no sense without a user, but a user can exist without a profile, so Profile holds userId.

Using Relations in Queries (a Preview)

// Create a post for an existing user, by setting the foreign key
await prisma.post.create({
  data: { title: "Relations", slug: "relations", authorId: 1 },
});

// Load a post together with its author
const post = await prisma.post.findUnique({
  where:   { slug: "relations" },
  include: { author: true },
});
console.log(post?.author.email);

Chapter 8 shows more ways to create related records together, and Chapter 9 covers reading related data properly with include and select.

Referential Actions: What Happens on Delete?

If you delete a user who has written posts, what should happen to the posts? The database enforces an answer, set with onDelete (and onUpdate, for when a referenced ID changes):

Action	On deleting the user…
`Restrict`	The delete is refused while the user still has posts
`NoAction`	Similar to Restrict; the exact timing of the check varies by database
`Cascade`	The user's posts are deleted too
`SetNull`	The posts stay, with `authorId` set to NULL (the relation must be optional)
`SetDefault`	The posts' `authorId` is set to its default value

If you don't choose	`onDelete`	`onUpdate`
Required relation	`Restrict`	`Cascade`
Optional relation	`SetNull`	`Cascade`

// Deleting a user also deletes their profile
user User @relation(fields: [userId], references: [id], onDelete: Cascade)

Choose Cascade deliberately

Cascade is convenient for data that's meaningless on its own, like a profile. For valuable data, it's dangerous: deleting one user could silently delete years of their posts, and their posts' comments, and so on down the chain. The default Restrict makes you decide explicitly what happens to the posts first.

Database differences

SQL Server doesn't offer Restrict (use NoAction) and rejects some cascade chains. On MySQL, SetDefault behaves like NoAction. MongoDB has no foreign keys, so Prisma enforces relations itself.

Hands-On Exercises

Exercise 1

Add the User–Post and User–Profile relations from this chapter to your schema, with profiles deleted along with their user. Migrate, generate, then create a user with a profile and two posts using foreign keys.

📄 View solution

Exercise 2

With the schema from Exercise 1, try to delete the user. What happens, and why? Then describe two different ways to make deleting a user possible, and when each is appropriate.

📄 View solution

Exercise 3

Add a Comment model: each comment belongs to one post and, optionally, to one user (guests can comment). Choose sensible onDelete behaviour for both relations and explain your choices.

📄 View solution

Chapter 7 Quick Reference

A relation has a relation scalar field (the foreign key, a real column) and relation fields on both sides (not stored)
@relation(fields: [authorId], references: [id]) goes on the side holding the foreign key
One-to-many: Post[] on one side, a single User on the other
One-to-one: the same, but with @unique on the foreign key and an optional side (Profile?)
Make a relation optional with Int? and User?; add @@index on foreign keys
onDelete/onUpdate: Restrict, NoAction, Cascade, SetNull, SetDefault
Defaults: required relations Restrict on delete; optional SetNull; both Cascade on update