Referential actions

Referential actions determine what happens to a record when your application deletes or updates a related record.

From version 2.26.0, you can define referential actions on the relation fields in your Prisma schema. This allows you to define referential actions like cascading deletes and cascading updates at a Prisma level.

Version differences
  • If you use version 3.0.1 or later, you can use referential actions as described on this page.
  • If you use a version between 2.26.0 and 3.0.0, you can use referential actions as described on this page, but you must enable the preview feature flag referentialActions.
  • If you use version 2.25.0 or earlier, you can do cascading deletes manually in the database.
  • 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.

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

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

    If you upgrade from a version earlier than 2.26.0: It is extremely important that you check the upgrade paths for referential actions section. Prisma support of referential actions 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 might 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 one-to-many relation between User and Post and a many-to-many relation between Post and Tag, with explicitly defined referential actions:

    schema.prisma
    1model User {
    2 id Int @id @default(autoincrement())
    3 posts Post[]
    4}
    5
    6model Post {
    7 id Int @id @default(autoincrement())
    8 title String
    9 tags TagOnPosts[]
    10 User User? @relation(fields: [userId], references: [id], onDelete: SetNull, onUpdate: Cascade)
    11 userId Int?
    12}
    13
    14model TagOnPosts {
    15 id Int @id @default(autoincrement())
    16 post Post? @relation(fields: [postId], references: [id], onUpdate: Cascade, onDelete: Cascade)
    17 tag Tag? @relation(fields: [tagId], references: [id], onUpdate: Cascade, onDelete: Cascade)
    18 postId Int?
    19 tagId Int?
    20}
    21
    22model Tag {
    23 id Int @id @default(autoincrement())
    24 name String @unique
    25 posts TagOnPosts[]
    26}

    This model explicitly defines the following referential actions:

    • If you delete a Tag, the corresponding tag assignment is also deleted in TagOnPosts, using the Cascade referential action
    • If you delete a User, the author is removed from all posts by setting the field value to Null, because of the SetNull referential action. To allow this, User and userId must be optional fields in Post.

    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 a User via the author relation:

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

    The schema does not explicitly define referential actions on the mandatory author relation field, which means that the default referential actions of Restrict for onDelete and Cascade for onUpdate apply.

    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.

    DatabaseCascadeRestrictNoActionSetNullSetDefault
    PostgreSQL✔️✔️✔️✔️✔️
    MySQL✔️✔️✔️✔️✔️
    SQLite✔️✔️✔️✔️✔️
    SQL Server✔️✔️✔️✔️
    MongoDB††✔️✔️✔️✔️
    • † On InnoDB, SetDefault is ignored and is silently replaced with NoAction instead.
    • NoAction can be used in place of Restrict when working with an SQL Server database.
    • †† Referential Actions for MongoDB are available from version 3.7.0 and later.

    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 dependent 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

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

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

    NoAction

    The NoAction action is similar to Restrict, the difference between the two is dependent 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.
    • SQL Server: When a referenced record is deleted or modified, an error is raised. See the SQL Server docs for more information.
    • MongoDB (in preview from version 3.6.0): When a record is modified or deleted, nothing is done to any related records.

    If you are emulating referential integrity in the Prisma client rather than using foreign keys in the database, you should be aware that currently Prisma only implements the referential actions. Foreign keys also create constraints, which make it impossible to manipulate data in a way that would violate these constraints: instead of executing the query, the database responds with an error. These constraints will not be created if you emulate referential integrity in the client, so if you set the referential action to NoAction there will be no checks to prevent you from breaking the referential integrity.

    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 User's id cannot be changed.

    SetNull

    • onDelete: SetNull The scalar field of the referencing 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 posts' authorUsername field values will be set to 'anonymous'.

    When the username of a User changes, its existing posts' authorUsername field values will be set to 'anonymous'.

    Upgrade paths from versions 2.25.0 and earlier

    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 or newer and enabled the preview feature flag, or upgraded to 3.0.0 or newer:

    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 default values from 2.25.0 and earlier.

    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 deleteMany() 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 database

    or

    • 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 added 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 one-to-many 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 without using referentialActions

    The following behavior applies in 2.25.0 and earlier, and since 2.26.0 until 3.0.0 without the referentialActions preview flag. That is, if the referential actions feature is not implemented.

    When invoking the delete() or deleteMany() 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 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