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.

Referential actions are currently a Preview feature.

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.

Enable the referentialActions Preview feature

Referential actions are a preview feature. To enable the referentialActions preview feature:

  1. Upgrade to Prisma version 2.26.0.

  2. Add referentialActions to the previewFeatures field of the Prisma Client generator block in the prisma.schema file:

    schema.prisma
    1generator client {
    2 provider = "prisma-client-js"
    3 previewFeatures = ["referentialActions"]
    4}
  3. Follow the upgrade paths for referential actions.

It is extremely important that you check the upgrade paths for referential actions section before you continue. The referentialActions Preview feature removes the safety net in Prisma Client that prevents cascading deletes at runtime. If you enable 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, 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 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".

Upgrade paths

There are a couple of paths you can take when upgrading which will give different results depending on the desired outcome.

If you currently use the migration workflow, you can run an introspection to check how the defaults are reflected in your schema. You can then manually update your database if you need to.

You can also decide to skip checking the the defaults and run a migration to update your database with the new default values.

The following assumes you have upgraded to 2.26.0 and enabled the preview feature flag.

Using Introspection

If you Introspect your database, the referential actions configured at the database level will be reflected in your Prisma Schema. If you have been using Prisma Migrate or prisma db push to manage the database schema, these are likely to be the <=2.25.0 default values.

When you run an Introspection, Prisma compares all the foreign keys in the database with the schema, if the SQL statements ON DELETE and ON UPDATE do not match the default values, they will be explicitly set in the schema file.

After introspecting, you can review the non-default clauses in your schema. The most important clause to review is onDelete, which defaults to Cascade in 2.25.0 and earlier.

If you are using either the delete() or deleteAll() methods, cascading deletes will now be performed as the referentialActions preview feature removed the safety net in Prisma Client that previously prevented cascading deletes at runtime. Be sure to check your code and make any adjustments accordingly.

Make sure you are happy with every case of onDelete: Cascade in your schema. If not, either:

  • Modify your Prisma schema and db push or dev migrate to change the databaseor
  • Manually update the underlying database if you use an introspection-only workflow

The following example would result in a cascading delete, if the User is deleted then all of their Post's will be deleted too.

A blog schema example

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[]
}

Using Migration

When running a Migration (or the prisma db push command) the new defaults will be applied to your database.

Unlike when you run an Introspect for the first time, the new referential actions clause and property, will not automatically be addded to your prisma schema by the Prisma VSCode extension. You will have to manually add them if you wish to use anything other than the new defaults.

Explicitly defining referential actions in your Prisma schema is optional. If you do not explicitly define a referential action for a relation, Prisma uses the new defaults.

Note that referential actions can be added on a case by case basis. This means that you can add them to one single relation and leave the rest set to the defaults by not manually specifying anything.

Checking for errors

Before upgrading to 2.26.0 and enabling the referential actions preview feature, Prisma prevented the deletion of records while using delete() or deleteMany() to preserve referential integrity. A custom runtime error would be thrown by the Prisma Client with the error code P2014.

After upgrading and enabling the referential actions preview feature, Prisma no longer performs runtime checks. You can instead specify a custom referential action to preserve the referential integrity between relations.

When you use NoAction or Restrict to prevent the deletion of records, the error messages will be different post 2.26.0 compared to pre 2.26.0. This is because they are now triggered by the database and not the Prisma Client. The new error code that can be expected is P2003.

To make sure you catch these new errors you can adjust your code accordingly.

Example of catching errors

The following example uses the below blog schema with a 1-m relationship between Post and User and sets a Restrict referential actions on the author field.

This means that if a user has a post, that user (and their posts) cannot be deleted.

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

Prior to upgrading and enabling the referential actions preview feature, the error code you would receive when trying to delete a user which has posts would be P2014 and it's message:

"The change you are trying to make would violate the required relation '{relation_name}' between the {model_a_name} and {model_b_name} models."

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
async function main() {
try {
await prisma.user.delete({
where: {
id: 'some-long-id',
},
})
} catch (error) {
if (error instanceof Prisma.PrismaClientKnownRequestError) {
if (error.code === 'P2014') {
console.log(error.message)
}
}
}
}
main()
.catch((e) => {
throw e
})
.finally(async () => {
await prisma.$disconnect()
})

To make sure you are checking for the correct errors in your code, modify your check to look for P2003, which will deliver the message:

"Foreign key constraint failed on the field: {field_name}"

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
async function main() {
try {
await prisma.user.delete({
where: {
id: 'some-long-id'
}
})
} catch (error) {
if (error instanceof Prisma.PrismaClientKnownRequestError) {
if (error.code === 'P2014') {
if (error.code === 'P2003') {
console.log(error.message)
}
}
}
}
main()
.catch(e => {
throw e
})
.finally(async () => {
await prisma.$disconnect()
})

Behavior in <=2.25.0 or without preview flag referentialActions

The following behavior applies in 2.25.0 and earlier, and since 2.26.0 without the referentialActions preview flag.

When invoking the delete() or deleteAll() methods using the Prisma Client on required relations, a runtime check is performed and the deletion of records prevented if they are referencing related objects. This prevents cascade behavior.

The current behavior, without upgrading and enabling the feature flag, does not allow setting referential actions at all.

The <=2.25.0 default referential actions

ClauseOptional relationsMandatory relations
onDeleteSetNullCascade
onUpdateCascadeCascade

If you need to actually use the cascade behavior configured in the database, you can use $raw SQL queries to delete multiple referenced records (such as users blog posts) and the deletion will be successful. This is because the Prisma Client will not perform runtime checks on raw queries.

Edit this page on GitHub