title	metaTitle	metaDescription
Cascading deletes (MySQL)	Configure cascading deletes with Prisma and MySQL	Learn how to configure cascading deletes with Prisma and MySQL by following the step-by-step instructions in this practical guide.

In 2.26.0 and later it is possible to do cascading deletes using the preview feature referential actions.

This page explains how to configure cascading deletes on foreign key constraints (relations) in your MySQL database.

Cascading deletes allow you to configure deletion behavior on relations (e.g. specify a rule like "when a user is deleted, all their posts should be automatically deleted too"). The database will then enforce this behavior when records are deleted.

There generally are five options for configuring deletion behavior in MySQL (paraphrasing from the MySQL docs):

RESTRICT (default): Prevents deleting the parent table if a child table references it.
NO ACTION: Same as RESTRICT in MySQL
CASCADE: When a row is deleted in the parent table, row(s) referencing it should be automatically deleted as well.
SET NULL: Causes the referencing columns to be set to NULL when the referenced row is deleted.
SET DEFAULT: Causes the referencing columns to be set to their default values when the referenced row is deleted. While this is recognized by MySQL, InnoDB (the most common MySQL database engine) and NDB do not allow the SET DEFAULT action.

In this guide, you'll create two tables where one references the other via a foreign key and explore the different options for cascading deletes.

At the end of the guide, you'll introspect your database to reflect the foreign key in the Prisma schema, then you'll generate Prisma Client and write a simple Node.js script to test the deletion behaviors.

Prerequisites

In order to follow this guide, you need:

a MySQL database server running
the mysql command line client for MySQL
the Node.js runtime for JavaScript installed on your machine

Note: If you are using a version of MySQL where InnoDB is not the default engine, you must specify ENGINE = InnoDB; at the end of each query.

1. Create a new database and project directory

Start by creating a project directory where you can put the files you'll create throughout this guide:

mkdir cascading-deletes-demo
cd cascading-deletes-demo

Next, make sure that your MySQL database server is running. Then execute the following command in your terminal to create a new database called CascadingDeletesDemo:

mysql -e 'CREATE DATABASE `CascadingDeletesDemo`'

You can validate that the database was created by running the SHOW TABLES command which lists all tables (relations) in your database (right now there are none):

mysql -e 'SHOW TABLES in `CascadingDeletesDemo`;'

2. Create two tables with a foreign key and `RESTRICT` deletion behavior

In this section, you'll create two tables where one references the other via a foreign key with the RESTRICT action in the CascadingDeletesDemo database.

Create a new file named cascading-deletes-restrict.sql and add the following code to it:

CREATE TABLE `CascadingDeletesDemo`.`User` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(256)
);

CREATE TABLE `CascadingDeletesDemo`.`Post` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(256),
  `author` INT,
  CONSTRAINT `author` FOREIGN KEY (`author`) REFERENCES `CascadingDeletesDemo`.`User`(`id`) ON DELETE RESTRICT
);

Note that because RESTRICT is the default, you could also omit it:

CREATE TABLE `CascadingDeletesDemo`.`Post` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(256),
  `authorId` INT,
  CONSTRAINT `author` FOREIGN KEY (`authorId`) REFERENCES `CascadingDeletesDemo`.`User`(`id`)
);

Alternatively, you could also use NO ACTION since RESTRICT and NO ACTION are identical in MySQL:

CREATE TABLE `CascadingDeletesDemo`.`Post` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(256),
  `authorId` INT,
  CONSTRAINT `author` FOREIGN KEY (`authorId`) REFERENCES `CascadingDeletesDemo`.`User`(`id`) ON DELETE NO ACTION
);

Now run the SQL statement against your database to create the two tables:

mysql < cascading-deletes-restrict.sql

Congratulations, you just created two tables called User and Post in the database. The Post table references the User table via the foreign key defined on the authorId column.

The deletion action RESTRICT in this case means that it is not possible to delete a User record that is referenced by a Post record. If you try doing that, the database will throw an error similar to this:

update or delete on table "User" violates foreign key constraint "Post_author_fkey" on table "Post"
Detail: Key (id)=(1) is still referenced from table "Post".

Alternative: Define the constraint as a column constraint

In the code above, you created the unique constraint as a table constraint. Alternatively, you can define it as a column constraint. There's no practical difference between the two, the alternative is just added for completeness.

To add the foreign key constraint as a column constraint, you need to adjust your SQL statement for creating the Post table to look as follows:

CREATE TABLE `CascadingDeletesDemo`.`Post` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(256),
  `author` INT REFERENCES `CascadingDeletesDemo`.`User`(`id`) ON DELETE RESTRICT
);

3. Create two tables with a foreign key and `CASCADE` deletion action

In this section, you'll create two tables where one references the other via a foreign key with the CASCADE action in the CascadingDeletesDemo database.

Create a new file named cascading-deletes-cascade.sql and add the following code to it:

CREATE TABLE `CascadingDeletesDemo`.`AnotherUser` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(256)
);

CREATE TABLE `CascadingDeletesDemo`.`AnotherPost` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(256),
  `authorId` INT,
  CONSTRAINT `author` FOREIGN KEY (`authorId`) REFERENCES `CascadingDeletesDemo`.`AnotherUser` (`id`) ON DELETE CASCADE
);

Now run the SQL statement against your database to create the two tables:

mysql < cascading-deletes-cascade.sql

Congratulations, you just created two tables called AnotherUser and AnotherPost in the database. The AnotherPost table references the AnotherUser table via the foreign key defined on the authorId column.

The deletion action CASCADE in this case means that when you delete a AnotherUser record that's referenced by one or more AnotherPost records, these AnotherPost records will be deleted as well.

4. Create two tables with a foreign key and `SET NULL` deletion behavior

In this section, you'll create two tables where one references the other via a foreign key with the SET NULL action in the CascadingDeletesDemo database.

Create a new file named cascading-deletes-set-null.sql and add the following code to it:

CREATE TABLE `CascadingDeletesDemo`.`AlmostTheLastUser` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(256)
);

CREATE TABLE `CascadingDeletesDemo`.`AlmostTheLastPost` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(256),
  `authorId` INT,
  CONSTRAINT `author` FOREIGN KEY (`authorId`) REFERENCES `CascadingDeletesDemo`.`AlmostTheLastUser`(`id`) ON DELETE SET NULL
);

Now run the SQL statement against your database to create the two tables:

mysql < cascading-deletes-set-null.sql

Congratulations, you just created two tables called AlmostTheLastUser and AlmostTheLastPost in the database. The AlmostTheLastPost table references the AlmostTheLastUser table via the foreign key defined on the authorId column.

The deletion behavior SET NULL in this case means that when you delete an AlmostTheLastUser record that's referenced by one or more AlmostTheLastPost records, the authorId column on these AlmostTheLastPost records will be set to NULL (therefore maintaining the integrity of the data and ensuring that no AlmostTheLastPost records point to non-existing AlmostTheLastUser records).

5. Create two tables with a foreign key and `SET DEFAULT` deletion behavior

In this section, you'll create two tables where one references the other via a foreign key with the SET DEFAULT action in the CascadingDeletesDemo database. Remember, InnoDB, the most common MySQL database engine does not allow the SET DEFAULT. We'll show this example for use with other database types, but delete the tables once we're done to clean our example up.

Create a new file named cascading-deletes-set-default.sql and add the following code to it:

CREATE TABLE `CascadingDeletesDemo`.`TheLastUser` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(256)
);

CREATE TABLE `CascadingDeletesDemo`.`TheLastPost` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(256),
  `authorId` INT DEFAULT 42,
  CONSTRAINT `author` FOREIGN KEY (`authorId`) REFERENCES `CascadingDeletesDemo`.`TheLastUser` (`id`) ON DELETE SET DEFAULT
);

Now run the SQL statement against your database to try to create the two tables:

mysql < cascading-deletes-set-default.sql

MySQL will raise the following error because InnoDB does not allow the SET DEFAULT action:

ERROR 1215 (HY000) at line 6: Cannot add foreign key constraint

When used on tables using a different database engine, the TheLastPost table would reference the TheLastUser table via the foreign key defined on the authorId column.

If you were using a different database engine, the deletion behavior SET DEFAULT would mean that when you delete a TheLastUser record that's referenced by one or more TheLastPost records, the authorId column on these TheLastPost records would be set to the default value of the column.

If no default exists, it would be set to NULL (in that case, the behavior would be identical to SET NULL). In the above table, a contrived example with a default value of 42 is used. If no TheLastUser record with an id value of 42 exists though, the operation would fail with a message similar to this:

insert or update on table "TheLastPost" violates foreign key constraint "TheLastPost_author_fkey"
Detail: Key (authorId)=(42) is not present in table "TheLastUser".

Since TheLastPost records would only ever reference existing TheLastUser records, SET NULL also maintains the integrity of the data and ensures that no TheLastPost records can point to non-existing TheLastUser records.

Since you were not able to create a TheLastPost table with the SET DEFAULT constraint, you can clean up the TheLastUser table:

mysql -e 'DROP TABLE `CascadingDeletesDemo`.`TheLastUser`;'

6. Introspect your database with Prisma

Deprecation warning
From Prisma 3.0.0 onwards, the prisma introspect command will be deprecated and replaced with the prisma db pull command.

In the previous sections, you created two tables with foreign key constraints for each of the following three scenarios:

The table Post uses RESTRICT action on the foreign key column authorId which points to the User table. This is identical to the default behavior and the NO ACTION behavior.
The table AnotherPost uses CASCADE action on the foreign key column authorId which points to the AnotherUser table.
The table AlmostTheLastPost uses SET NULL action on the foreign key column authorId which points to the AlmostTheLastUser table

In addition to the above behavior, you also confirmed that the default database engine (InnoDB) does not allow for the SET DEFAULT action on delete. The relationship between the following tables was not allowed:

The table TheLastPost used the SET DEFAULT action on the foreign key column authorId which pointed to the TheLastUser table

In this section you'll introspect your database to generate the Prisma models for these tables.

To start, set up a new Node.js project and add the prisma CLI as a development dependency:

npm init -y
npm install prisma --save-dev

In order to introspect your database, you need to tell Prisma how to connect to it. You do so by configuring a datasource in your Prisma schema.

Create a new file named schema.prisma and add the following code to it:

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

The database connection URL is set via an environment variable. The Prisma CLI automatically supports the dotenv format which automatically picks up environment variables defined in a file named .env.

Create a new file named .env and set your database connection URL as the DATABASE_URL environment variable:

DATABASE_URL=mysql://__USER__:__PASSWORD__@__HOST__:__PORT__/CascadingDeletesDemo

In the above code snippet, you need to replace the uppercase placeholders with your own connection details. For example, if your database is running locally it could look like this:

DATABASE_URL=mysql://janedoe:mypassword@localhost:3306/CascadingDeletesDemo

With both the schema.prisma and .env files in place, you can run Prisma's introspection with the following command:

npx prisma introspect

This command introspects your database and for each table adds a Prisma model to the Prisma schema:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

model AlmostTheLastPost {
  authorId          Int?
  id                Int                @default(autoincrement()) @id
  title             String?
  AlmostTheLastUser AlmostTheLastUser? @relation(fields: [authorId], references: [id])

  @@index([authorId], name: "author4")
}

model AlmostTheLastUser {
  id                Int                 @default(autoincrement()) @id
  name              String?
  AlmostTheLastPost AlmostTheLastPost[]
}

model AnotherPost {
  authorId    Int?
  id          Int          @default(autoincrement()) @id
  title       String?
  AnotherUser AnotherUser? @relation(fields: [authorId], references: [id])

  @@index([authorId], name: "author")
}

model AnotherUser {
  id          Int           @default(autoincrement()) @id
  name        String?
  AnotherPost AnotherPost[]
}

model Post {
  author Int?
  id     Int     @default(autoincrement()) @id
  title  String?
  User   User?   @relation(fields: [author], references: [id])

  @@index([author], name: "author2")
}

model User {
  id   Int     @default(autoincrement()) @id
  name String?
  Post Post[]
}

Note: Deletion behaviors for relations are not yet supported in the Prisma schema so you don't see them anywhere. The behavior will still be enforced by the database though since that's where you configured it.

7. Generate Prisma Client

To validate whether the foreign key constraints work, you'll now generate Prisma Client and send a few sample queries to the database to test the relations.

First, add a generator block to your Prisma schema (typically added right below the datasource block):

generator client {
  provider = "prisma-client-js"
}

Run the following command to install and generate Prisma Client in your project:

npx prisma generate

Now you can use Prisma Client to send database queries in Node.js.

8. Validate the deletion behavior in a Node.js script

8.1. Validating `RESTRICT`

The RESTRICT keyword prevents deletion of a referenced row. So, when trying to delete a User record that is reference by a Post record, the query will fail.

To test the RESTRICT behavior, you need to access the User and Post tables.

Create a new file named index.js and add the following code to it:

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

async function main() {
  const userWithPost = await prisma.user.create({
    data: {
      name: 'Alice',
      Post: {
        create: { title: 'Hello World' },
      },
    },
  })

  try {
    const deletedUser = await prisma.user.delete({
      where: { id: userWithPost.id },
    })
  } catch (e) {
    console.log(e)
  }
}

main()

Run the code with this command:

node index.js

The script will throw an exception when invoking prisma.user.delete(...). The error will look similar to this:

PrismaClientUnknownRequestError:
Invalid `const deletedUser = await prisma.user.delete()` invocation in
/Users/janedoe/cascading-deletes-demo/index.js:16:49

  12   },
  13 });
  14
  15 try {
→ 16   const deletedUser = await prisma.user.delete(

Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: ForeignKeyConstraintViolation { constraint: Fields(["authorId"]) } })
    at PrismaClientFetcher.request (/Users/janedoe/cascading-deletes-demo/mysql/node_modules/@prisma/client/runtime/index.js:1:51487)
    at processTicksAndRejections (internal/process/task_queues.js:85:5)

8.2. Validating `CASCADE`

The CASCADE keyword ensures that when a referenced row is deleted, all the rows that are referencing it get deleted as well. So, when trying to delete an AnotherUser record that is referenced by one or more AnotherPost records, these AnotherPost records will be deleted too.

To test the CASCADE behavior, you need to access the AnotherUser and AnotherPost tables.

Open the index.js file and replace its contents with the following code:

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

async function main() {
  const userWithPost = await prisma.anotherUser.create({
    data: {
      name: 'Alice',
      AnotherPost: {
        create: { title: 'Hello World' },
      },
    },
  })

  const allUsers1 = await prisma.anotherUser.findMany()
  const allPosts1 = await prisma.anotherPost.findMany()

  console.log(
    `Before deleting the \`AnotherUser\` record there are ${allUsers1.length} users and ${allPosts1.length} posts.`
  )

  const deletedUser = await prisma.anotherUser.delete({
    where: { id: userWithPost.id },
  })

  const allUsers2 = await prisma.anotherUser.findMany()
  const allPosts2 = await prisma.anotherPost.findMany()

  console.log(
    `After deleting the \`AnotherUser\` record there are ${allUsers2.length} users and ${allPosts2.length} posts.`
  )
}

main()

Run the code with this command:

node index.js

The console output will look similar to this:

Before deleting the `AnotherUser` record there are 1 users and 1 posts.
After deleting the `AnotherUser` record there are 0 users and 0 posts.

8.3. Validating `SET NULL`

The SET NULL keyword ensures that when a referenced row is deleted, all the rows that are referencing it reset their foreign key to NULL. So, when deleting an AlmostTheLastUser record that is referenced by one or more AlmostTheLastPost records, these AlmostTheLastPost records will reset their authorId fields to NULL.

To test the SET NULL behavior, you need to access the AlmostTheLastUser and AlmostTheLastPost tables.

Open the index.js file and replace its contents with the following code:

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient()

async function main() {
  const userWithPost = await prisma.almostTheLastUser.create({
    data: {
      name: 'Alice',
      AlmostTheLastPost: {
        create: { title: 'Hello World' },
      },
    },
    include: {
      AlmostTheLastPost: true,
    },
  })

  const postBefore = await prisma.almostTheLastPost.findUnique({
    where: { id: userWithPost.AlmostTheLastPost[0].id },
    include: { AlmostTheLastUser: true },
  })

  console.log(
    `Before deleting the \`AlmostTheLastUser\` record, the \`AlmostTheLastPost\` record looks as follows:\n${JSON.stringify(
      postBefore
    )}`
  )

  const deletedUser = await prisma.almostTheLastUser.delete({
    where: { id: userWithPost.id },
  })

  const postAfter = await prisma.almostTheLastPost.findUnique({
    where: { id: postBefore.id },
    include: { AlmostTheLastUser: true },
  })

  console.log(
    `After deleting the \`AlmostTheLastUser\` record, the \`AlmostTheLastPost\` record looks as follows:\n${JSON.stringify(
      postAfter
    )}`
  )
}

main()

Run the code with this command:

node index.js

The console output will look similar to this:

Before deleting the `AlmostTheLastUser` record, the `AlmostTheLastPost` record looks as follows:
{"authorId":1,"id":1,"title":"Hello World","AlmostTheLastUser":{"id":1,"name":"Alice"}}
After deleting the `AlmostTheLastUser` record, the `AlmostTheLastPost` record looks as follows:
{"authorId":null,"id":1,"title":"Hello World","AlmostTheLastUser":null}

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

02-mysql.mdx

02-mysql.mdx

Prerequisites

1. Create a new database and project directory

2. Create two tables with a foreign key and `RESTRICT` deletion behavior

3. Create two tables with a foreign key and `CASCADE` deletion action

4. Create two tables with a foreign key and `SET NULL` deletion behavior

5. Create two tables with a foreign key and `SET DEFAULT` deletion behavior

6. Introspect your database with Prisma

7. Generate Prisma Client

8. Validate the deletion behavior in a Node.js script

8.1. Validating `RESTRICT`

8.2. Validating `CASCADE`

8.3. Validating `SET NULL`

Files

02-mysql.mdx

Latest commit

History

02-mysql.mdx

File metadata and controls

Prerequisites

1. Create a new database and project directory

2. Create two tables with a foreign key and RESTRICT deletion behavior

3. Create two tables with a foreign key and CASCADE deletion action

4. Create two tables with a foreign key and SET NULL deletion behavior

5. Create two tables with a foreign key and SET DEFAULT deletion behavior

6. Introspect your database with Prisma

7. Generate Prisma Client

8. Validate the deletion behavior in a Node.js script

8.1. Validating RESTRICT

8.2. Validating CASCADE

8.3. Validating SET NULL

2. Create two tables with a foreign key and `RESTRICT` deletion behavior

3. Create two tables with a foreign key and `CASCADE` deletion action

4. Create two tables with a foreign key and `SET NULL` deletion behavior

5. Create two tables with a foreign key and `SET DEFAULT` deletion behavior

8.1. Validating `RESTRICT`

8.2. Validating `CASCADE`

8.3. Validating `SET NULL`