# Referential actions (/docs/orm/v6/prisma-schema/data-model/relations/referential-actions)
Location: ORM > v6 > Prisma Schema > Data Model > Relations > 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 ORM level.
> [!NOTE]
> **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](/orm/v6/reference/preview-features/client-preview-features#enabling-a-prisma-client-preview-feature) `referentialActions`.
> * If you use version 2.25.0 or earlier, you can configure cascading deletes manually in your 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.
```prisma title="schema.prisma" highlight=4;normal showLineNumbers
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 ORM [uses a default](#referential-action-defaults).
Questions answered in this page
* What do referential actions do?
* Which defaults apply if none are set?
* How do actions map to my database?
> [!CAUTION]
> If you upgrade from a version earlier than 2.26.0:
> It is extremely important that you check the [upgrade paths for referential actions](/orm/v6/more/upgrades/older-versions#referential-actions) section. Prisma ORM's 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](/orm/v6/more/upgrades/older-versions#referential-actions) - `ON DELETE CASCADE` - becomes active. This might result in cascading deletes that you did not expect.
What are referential actions? [#what-are-referential-actions]
Referential actions are policies that define how a referenced record is handled by the database when you run an [`update`](/orm/v6/prisma-client/queries/crud#update) or [`delete`](/orm/v6/prisma-client/queries/crud#delete) query.
Referential actions on the database level
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](/orm/v6/prisma-schema/data-model/relations#relation-fields), **which do not exist on the database**, and [scalar fields](/orm/v6/prisma-schema/data-model/models#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 [#how-to-use-referential-actions]
Referential actions are defined in the [`@relation`](/orm/v6/reference/prisma-schema-reference#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 ORM falls back to a default](#referential-action-defaults).
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:
```prisma title="schema.prisma" highlight=10,16-17;normal showLineNumbers
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[]
}
```
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 ORM supports the following referential actions:
* [`Cascade`](#cascade)
* [`Restrict`](#restrict)
* [`NoAction`](#noaction)
* [`SetNull`](#setnull)
* [`SetDefault`](#setdefault)
Referential action defaults [#referential-action-defaults]
If you do not specify a referential action, Prisma ORM uses the following defaults:
| Clause | Optional relations | Mandatory relations |
| :--------- | :----------------- | :------------------ |
| `onDelete` | `SetNull` | `Restrict` |
| `onUpdate` | `Cascade` | `Cascade` |
For example, in the following schema all `Post` records must be connected to a `User` via the `author` relation:
```prisma highlight=4;normal
model Post {
id Int @id @default(autoincrement())
title String
author User @relation(fields: [authorId], references: [id]) // [!code highlight]
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 [#caveats]
The following caveats apply:
* Referential actions are **not** supported on [implicit many-to-many relations](/orm/v6/prisma-schema/data-model/relations/many-to-many-relations#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](/orm/v6/prisma-schema/data-model/relations/troubleshooting-relations#how-to-use-a-relation-table-with-a-many-to-many-relationship).
* 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](https://github.com/prisma/prisma/issues/7909) for more information.
Types of referential actions [#types-of-referential-actions]
The following table shows which referential action each database supports.
| Database | Cascade | Restrict | NoAction | SetNull | SetDefault |
| :------------ | :------ | :------- | :------- | :------ | :--------- |
| PostgreSQL | ✔️ | ✔️ | ✔️ | ✔️⌘ | ✔️ |
| MySQL/MariaDB | ✔️ | ✔️ | ✔️ | ✔️ | ❌ (✔️†) |
| SQLite | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ |
| SQL Server | ✔️ | ❌‡ | ✔️ | ✔️ | ✔️ |
| CockroachDB | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ |
| MongoDB†† | ✔️ | ✔️ | ✔️ | ✔️ | ❌ |
* † See [special cases for MySQL](#mysqlmariadb).
* ⌘ See [special cases for PostgreSQL](#postgresql).
* ‡ See [special cases for SQL Server](#sql-server).
* †† Referential actions for MongoDB are available in Prisma ORM versions 3.7.0 and later.
Special cases for referential actions [#special-cases-for-referential-actions]
Referential actions are part of the ANSI SQL standard. However, there are special cases where some relational databases diverge from the standard.
MySQL/MariaDB [#mysqlmariadb]
MySQL/MariaDB, and the underlying InnoDB storage engine, does not support `SetDefault`. The exact behavior depends on the database version:
* In MySQL versions 8 and later, and MariaDB versions 10.5 and later, `SetDefault` effectively acts as an alias for `NoAction`. You can define tables using the `SET DEFAULT` referential action, but a foreign key constraint error is triggered at runtime.
* In MySQL versions 5.6 and later, and MariaDB versions before 10.5, attempting to create a table definition with the `SET DEFAULT` referential action fails with a syntax error.
For this reason, when you set `mysql` as the database provider, Prisma ORM warns users to replace `SetDefault` referential actions in the Prisma schema with another action.
PostgreSQL [#postgresql]
PostgreSQL is the only database supported by Prisma ORM that allows you to define a `SetNull` referential action that refers to a non-nullable field. However, this raises a foreign key constraint error when the action is triggered at runtime.
For this reason, when you set `postgres` as the database provider in the (default) `foreignKeys` relation mode, Prisma ORM warns users to mark as optional any fields that are included in a `@relation` attribute with a `SetNull` referential action. For all other database providers, Prisma ORM rejects the schema with a validation error.
SQL Server [#sql-server]
[`Restrict`](#restrict) is not available for SQL Server databases, but you can use [`NoAction`](#noaction) instead.
Cascade [#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 [#example-usage]
```prisma title="schema.prisma" highlight=4;add showLineNumbers
model Post {
id Int @id @default(autoincrement())
title String
author User @relation(fields: [authorId], references: [id], onDelete: Cascade, onUpdate: Cascade) // [!code ++]
authorId Int
}
model User {
id Int @id @default(autoincrement())
posts Post[]
}
```
Result of using Cascade [#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 [#how-to-use-cascading-deletes]
[Watch video](https://www.youtube.com/watch?v=-Nv3wSm0Ac0)
Restrict [#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 [#example-usage-1]
```prisma title="schema.prisma" highlight=4;add showLineNumbers
model Post {
id Int @id @default(autoincrement())
title String
author User @relation(fields: [authorId], references: [id], onDelete: Restrict, onUpdate: Restrict) // [!code ++]
authorId Int
}
model User {
id Int @id @default(autoincrement())
posts Post[]
}
```
Result of using Restrict [#result-of-using-restrict]
`User`s with posts **cannot** be deleted. The `User`'s `id` **cannot** be changed.
> [!WARNING]
> The `Restrict` action is **not** available on [Microsoft SQL Server](/orm/v6/overview/databases/sql-server) and triggers a schema validation error. Instead, you can use [`NoAction`](#noaction), which produces the same result and is compatible with SQL Server.
NoAction [#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](https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK) for more information.
* **MySQL**: `NoAction` behaves exactly the same as `Restrict`. See [the MySQL docs](https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html#foreign-key-referential-actions) for more information.
* **SQLite**: When a related primary key is modified or deleted, no action is taken. See [the SQLite docs](https://www.sqlite.org/foreignkeys.html#fk_actions) for more information.
* **SQL Server**: When a referenced record is deleted or modified, an error is raised. See [the SQL Server docs](https://learn.microsoft.com/en-us/sql/relational-databases/tables/graph-edge-constraints?view=sql-server-ver15#on-delete-referential-actions-on-edge-constraints) 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.
> [!WARNING]
> If you are [managing relations in Prisma Client](/orm/v6/prisma-schema/data-model/relations/relation-mode#emulate-relations-in-prisma-orm-with-the-prisma-relation-mode) rather than using foreign keys in the database, you should be aware that currently Prisma ORM 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 Prisma 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 [#example-usage-2]
```prisma title="schema.prisma" highlight=4;add showLineNumbers
model Post {
id Int @id @default(autoincrement())
title String
author User @relation(fields: [authorId], references: [id], onDelete: NoAction, onUpdate: NoAction) // [!code ++]
authorId Int
}
model User {
id Int @id @default(autoincrement())
posts Post[]
}
```
Result of using NoAction [#result-of-using-noaction]
`User`'s with posts **cannot** be deleted. The `User`'s `id` **cannot** be changed.
SetNull [#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.
```prisma title="schema.prisma" highlight=4;add showLineNumbers
model Post {
id Int @id @default(autoincrement())
title String
author User? @relation(fields: [authorId], references: [id], onDelete: SetNull, onUpdate: SetNull) // [!code ++]
authorId Int?
}
model User {
id Int @id @default(autoincrement())
posts Post[]
}
```
Result of using SetNull [#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 [#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`](/orm/v6/reference/prisma-schema-reference#default). If no defaults are provided for any of the scalar fields, a runtime error will be thrown.
```prisma title="schema.prisma" highlight=4,5;add showLineNumbers
model Post {
id Int @id @default(autoincrement())
title String
authorUsername String? @default("anonymous") // [!code ++]
author User? @relation(fields: [authorUsername], references: [username], onDelete: SetDefault, onUpdate: SetDefault) // [!code ++]
}
model User {
username String @id
posts Post[]
}
```
Result of using SetDefault [#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'.
Database-specific requirements [#database-specific-requirements]
MongoDB and SQL Server have specific requirements for referential actions if you have [self-relations](/orm/v6/prisma-schema/data-model/relations/referential-actions/special-rules-for-referential-actions#self-relation-sql-server-and-mongodb) or [cyclic relations](/orm/v6/prisma-schema/data-model/relations/referential-actions/special-rules-for-referential-actions#cyclic-relation-between-three-tables-sql-server-and-mongodb) in your data model. SQL Server also has specific requirements if you have relations with [multiple cascade paths](/orm/v6/prisma-schema/data-model/relations/referential-actions/special-rules-for-referential-actions#multiple-cascade-paths-between-two-models-sql-server-only).
Upgrade paths from versions 2.25.0 and earlier [#upgrade-paths-from-versions-2250-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 defaults and run a migration to update your database with the [new default values](#referential-action-defaults).
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 [#using-introspection]
If you [Introspect](/orm/v6/prisma-schema/introspection) 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](#referential-action-defaults) from 2.25.0 and earlier.
When you run an Introspection, Prisma ORM 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.
> [!WARNING]
> If you are using either the [`delete()`](/orm/v6/prisma-client/queries/crud#delete-a-single-record) or [`deleteMany()`](/orm/v6/prisma-client/queries/crud#delete-all-records) methods, **[cascading deletes](#how-to-use-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 [#a-blog-schema-example]
```prisma showLineNumbers
model Post {
id Int @id @default(autoincrement())
title String
author User @relation(fields: [authorId], references: [id], onDelete: Cascade) // [!code ++]
authorId Int
}
model User {
id Int @id @default(autoincrement())
posts Post[]
}
```
Using Migration [#using-migration]
When running a [Migration](/orm/v6/prisma-migrate/getting-started) (or the [`prisma db push`](/orm/v6/prisma-migrate/workflows/prototyping-your-schema) command) the [new defaults](#referential-action-defaults) will be applied to your database.
> [!NOTE]
> 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 ORM uses the [new defaults](#referential-action-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 [#checking-for-errors]
**Before** upgrading to 2.26.0 and enabling the referential actions **preview feature**, Prisma ORM prevented the deletion of records while using `delete()` or `deleteMany()` to preserve referential integrity. A custom runtime error would be thrown by Prisma Client with the error code `P2014`.
**After** upgrading and enabling the referential actions **preview feature**, Prisma ORM no longer performs runtime checks. You can instead specify a custom referential action to preserve the referential integrity between relations.
When you use [`NoAction`](#noaction) or [`Restrict`](#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** 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 [#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`](#restrict) referential actions on the `author` field.
This means that if a user has a post, that user (and their posts) **cannot** be deleted.
```prisma title="schema.prisma" showLineNumbers
model Post {
id Int @id @default(autoincrement())
title String
author User @relation(fields: [authorId], references: [id], onDelete: Restrict)
authorId String
}
model User {
id Int @id @default(autoincrement())
posts Post[]
}
```
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."
```ts
import { PrismaClient } from "../prisma/generated/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();
```
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}"
```ts highlight=14;delete|15;add
import { PrismaClient } from '../prisma/generated/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') { // [!code --]
if (error.code === 'P2003') { // [!code ++]
console.log(error.message)
}
}
}
}
main()
```
## Related pages
- [`Many-to-many relations`](https://www.prisma.io/docs/orm/v6/prisma-schema/data-model/relations/many-to-many-relations): How to define and work with many-to-many relations in Prisma.
- [`One-to-many relations`](https://www.prisma.io/docs/orm/v6/prisma-schema/data-model/relations/one-to-many-relations): How to define and work with one-to-many relations in Prisma.
- [`One-to-one relations`](https://www.prisma.io/docs/orm/v6/prisma-schema/data-model/relations/one-to-one-relations): How to define and work with one-to-one relations in Prisma.
- [`Relation mode`](https://www.prisma.io/docs/orm/v6/prisma-schema/data-model/relations/relation-mode): Manage relations between records with relation modes in Prisma
- [`Self-relations`](https://www.prisma.io/docs/orm/v6/prisma-schema/data-model/relations/self-relations): How to define and work with self-relations in Prisma.