Referential actions

In 2.26.0 and later, you can define referential actions on your relation fields. Referential actions determine what should happen to a record when a related record is deleted or updated.

In the following example, adding onDelete: Cascade to the author field on the Post model means that deleting the User record will also delete all related Post records.

model Post {
id Int @id @default(autoincrement())
title String
author User @relation(fields: [authorId], references: [id], onDelete: Cascade)
authorId Int
}
model User {
id Int @id @default(autoincrement())
posts Post[]
}

If you do not specify a referential action, Prisma uses a default.

It is extremely important that you check the upgrade paths for referential actions section before you continue. The referentialActions feature removes the safety net in Prisma Client that prevents cascading deletes at runtime. If you use the feature without upgrading your database, the old default action - ON DELETE CASCADE - becomes active. This may result in cascading deletes that you did not expect.

What are referential actions?

Referential actions are policies that define how a referenced record is handled by the database when you run an update or delete query.

Referential actions are features of foreign key constraints that exist to preserve referential integrity in your database.

When you define relationships between data models in your Prisma schema, you use [relation fields/concepts/components/prisma-schema/#relation-fields), which do not exist on the database, and scalar fields, which do exist on the database. These foreign keys connect the models on the database level.

Referential integrity states that these foreign keys must reference an existing primary key value in the related database table. In your Prisma schema, this is generally represented by the id field on the related model.

By default a database will reject any operation that violates the referential integrity, for example, by deleting referenced records.

How to use referential actions

Referential actions are defined in the @relation attribute and map to the actions on the foreign key constraint in the underlying database. If you do not specify a referential action, Prisma falls back to a default.

The following model defines a 1-m relation between User and Post and a n-m relation between Post and Tag, with explicitly defined referential actions:

model User {
id Int @id @default(autoincrement())
posts Post[]
}
model Post {
id Int @id @default(autoincrement())
title String
tags TagOnPosts[]
User User? @relation(fields: [userId], references: [id], onDelete: SetNull, onUpdate: Cascade)
userId Int?
}
model TagOnPosts {
id Int @id @default(autoincrement())
post Post? @relation(fields: [postId], references: [id], onUpdate: Cascade, onDelete: Cascade)
tag Tag? @relation(fields: [tagId], references: [id], onUpdate: Cascade, onDelete: Cascade)
postId Int?
tagId Int?
}
model Tag {
id Int @id @default(autoincrement())
name String @unique
posts TagOnPosts[]
}
  • If you delete a Tag, the corresponding tag assignment is also deleted TagOnPost (Cascade)
  • If you delete a User, the author is removed from all posts (SetNull) - User and userId must be optional

Prisma supports the following referential actions:

Referential action defaults

If you do not specify a referential action, Prisma uses the following defaults:

ClauseOptional relationsMandatory relations
onDeleteSetNullRestrict
onUpdateCascadeCascade

For example - in the following schema, all Post records must be connected to an author. The schema does not explicitly define referential actions on the author field, which means that default referential actions apply (see SQL tab):

schema.prisma
SQL
model Post {
id Int @id @default(autoincrement())
title String
author User @relation(fields: [authorId], references: [id])
authorId Int
}
model User {
id Int @id @default(autoincrement())
posts Post[]
}

Caveats

The following caveats apply:

  • Referential actions are not supported on implicit many-to-many relations. To use referential actions, you must define an explicit many-to-many relation and define your referential actions on the join table.
  • Certain combinations of referential actions and required/optional relations are incompatible. For example, using SetNull on a required relation will lead to database errors when deleting referenced records because the non-nullable constraint would be violated. See this GitHub issue for more information.

Types of referential actions

The following table shows which referential action each database supports.

Note that NoAction can be used in replace of Restrict when working with an SQLServer database.

DatabaseCascadeRestrictNoActionSetNullSetDefault
PostgreSQL✔️✔️✔️✔️✔️
MySQL✔️✔️✔️✔️✔️
SQLite✔️✔️✔️✔️✔️
SQLServer✔️✔️✔️✔️
  • † On InnoDB, SetDefault will be ignored and silently use NoAction.

Cascade

  • onDelete: Cascade Deleting a referenced record will trigger the deletion of referencing record.

  • onUpdate: Cascade Updates the relation scalar fields if the referenced scalar fields of the dependant record are updated.

Example usage

schema.prisma
1model Post {
2 id Int @id @default(autoincrement())
3 title String
+ author User @relation(fields: [authorId], references: [id], onDelete: Cascade, onUpdate: Cascade)
5 authorId Int
6}
7
8model User {
9 id Int @id @default(autoincrement())
10 posts Post[]
11}
Result of using Cascade

If a User record is deleted, then their posts are deleted too. If the user's id is updated, then the corresponding authorId is also updated.

How to use cascading deletes

Restrict

  • onDelete: Restrict Prevents the deletion if any referencing records exist.

  • onUpdate: Restrict Prevents the identifier of a referenced record from being changed.

Example usage

schema.prisma
1model Post {
2 id Int @id @default(autoincrement())
3 title String
+ author User @relation(fields: [authorId], references: [id], onDelete: Restrict, onUpdate: Restrict)
5 authorId Int
6}
7
8model User {
9 id Int @id @default(autoincrement())
10 posts Post[]
11}
Result of using Restrict

User's with posts cannot be deleted. The Post's id cannot be changed.

The Restrict action is not available on Microsoft SQL Server and will trigger a schema validation error. Instead, you can use NoAction, which produces the same result and is compatible with SQLServer.

NoAction

The NoAction action is similar to Restrict, the difference between the two is dependant on the database being used:

  • PostgreSQL: NoAction allows the check (if a referenced row on the table exists) to be deferred until later in the transaction. See the PostgreSQL docs for more information.
  • MySQL: NoAction behaves exactly the same as Restrict. See the MySQL docs for more information.
  • SQLite: When a related primary key is modified or deleted, no action is taken. See the SQLite docs for more information.
  • SQLServer: When a referenced record is deleted or modified, an error is raised. See the SQLServer docs for more information.

Example usage

schema.prisma
1model Post {
2 id Int @id @default(autoincrement())
3 title String
+ author User @relation(fields: [authorId], references: [id], onDelete: NoAction, onUpdate: NoAction)
5 authorId Int
6}
7
8model User {
9 id Int @id @default(autoincrement())
10 posts Post[]
11}
Result of using NoAction

User's with posts cannot be deleted. The Post's id cannot be changed.

SetNull

  • onDelete: SetNull The scalar field of the referenced object will be set to NULL.

  • onUpdate: SetNull When updating the identifier of a referenced object, the scalar fields of the referencing objects will be set to NULL.

SetNull will only work on optional relations. On required relations, a runtime error will be thrown since the scalar fields cannot be null.

schema.prisma
1model Post {
2 id Int @id @default(autoincrement())
3 title String
+ author User? @relation(fields: [authorId], references: [id], onDelete: SetNull, onUpdate: SetNull)
5 authorId Int?
6}
7
8model User {
9 id Int @id @default(autoincrement())
10 posts Post[]
11}
Result of using SetNull

When deleting a User, the authorId will be set to NULL for all its authored posts.

When changing a User's id, the authorId will be set to NULL for all its authored posts.

SetDefault

  • onDelete: SetDefault The scalar field of the referencing object will be set to the fields default value.

  • onUpdate: SetDefault The scalar field of the referencing object will be set to the fields default value.

These require setting a default for the relation scalar field with @default. If no defaults are provided for any of the scalar fields, a runtime error will be thrown.

schema.prisma
1model Post {
2 id Int @id @default(autoincrement())
3 title String
+ authorUsername String? @default("anonymous")
+ author User? @relation(fields: [authorUsername], references: [username], onDelete: SetDefault, onUpdate: SetDefault)
6}
7
8model User {
9 username String @id
10 posts Post[]
11}
Result of using SetDefault

When deleting a User, its existing authored posts will now be authored by a User with id "anonymous".

When the username of a User changes, its existing authored posts will now be authored by User with username "anonymous".

Edit this page on GitHub