Cascading deletes (SQLite)

Overview

This page explains how to configure cascading deletes on foreign key constraints (relations) in your SQLite 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 SQLite (paraphrasing from the SQLite docs):

• NO ACTION (default): If any referencing rows still exist when the constraint is checked, an error is raised
• RESTRICT: Prevents deletion of a referenced row. The difference between these two choices is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not.
• 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. Note that these do not excuse you from observing any constraints. For example, if an action specifies SET DEFAULT but the default value would not satisfy the foreign key, the operation will fail.
• CASCADE: When a referenced row is deleted, row(s) referencing it should be automatically deleted as well.

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:

• the sqlite3 command line client for SQLite
• the Node.js runtime for JavaScript installed on your machine

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 SQLite database server is running. Then execute the following command in your terminal to create a new database called CascadingDeletesDemo:

sqlite3 CascadingDeletesDemo.db '.databases'

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

sqlite3 CascadingDeletesDemo.db '.tables'

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 called cascading-deletes-restrict.sql and add the following code to it:

CREATE TABLE "User" (
  "id" INTEGER PRIMARY KEY,
  "name" TEXT
);

CREATE TABLE "Post" (
  "id" INTEGER PRIMARY KEY,
  "title" TEXT,
  "authorId" INTEGER,
  FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE RESTRICT
);

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

sqlite3 CascadingDeletesDemo.db < 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 behavior RESTRICT in this case means that it is not possible to delete a User record that is referenced User 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".

CREATE TABLE "Post" (
  "id" INTEGER PRIMARY KEY,
  "title" TEXT,
  "author" INTEGER REFERENCES "User" ("id") ON DELETE RESTRICT
);

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

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 called cascading-deletes-cascade.sql and add the following code to it:

CREATE TABLE "AnotherUser" (
  "id" INTEGER PRIMARY KEY,
  "name" TEXT
);

CREATE TABLE "AnotherPost" (
  "id" INTEGER PRIMARY KEY,
  "title" TEXT,
  "authorId" INTEGER,
  FOREIGN KEY ("authorId") REFERENCES "AnotherUser" ("id") ON DELETE CASCADE
);

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

sqlite3 CascadingDeletesDemo.db < 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 behavior CASCADE in this case means that when you delete an 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 NO ACTION deletion behavior

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

Create a new file called cascading-deletes-no-action.sql and add the following code to it:

CREATE TABLE "OneMoreUser" (
  "id" INTEGER PRIMARY KEY,
  "name" TEXT
);

CREATE TABLE "OneMorePost" (
  "id" INTEGER PRIMARY KEY,
  "title" TEXT,
  "authorId" INTEGER,
  FOREIGN KEY ("authorId") REFERENCES "OneMoreUser" ("id") ON DELETE NO ACTION
);

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

CREATE TABLE "OneMorePost" (
  "id" INTEGER PRIMARY KEY,
  "title" TEXT,
  "authorId" INTEGER,
  FOREIGN KEY ("authorId") REFERENCES "OneMoreUser" ("id")
);

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

sqlite3 CascadingDeletesDemo.db < cascading-deletes-no-action.sql

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

The deletion behavior NO ACTION in this case means that it is not possible to delete a OneMoreUser record that is referenced OneMoreUser 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".

5. 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 called cascading-deletes-set-null.sql and add the following code to it:

CREATE TABLE "AlmostTheLastUser" (
  "id" INTEGER PRIMARY KEY,
  "name" TEXT
);

CREATE TABLE "AlmostTheLastPost" (
  "id" INTEGER PRIMARY KEY,
  "title" TEXT,
  "authorId" INTEGER,
  FOREIGN KEY ("authorId") REFERENCES "AlmostTheLastUser" ("id") ON DELETE SET NULL
);

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

sqlite3 CascadingDeletesDemo.db < 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 a 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).

6. 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.

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

CREATE TABLE "TheLastUser" (
  "id" INTEGER PRIMARY KEY,
  "name" TEXT
);

CREATE TABLE "TheLastPost" (
  "id" INTEGER PRIMARY KEY,
  "title" TEXT,
  "authorId" INTEGER DEFAULT 42,
  FOREIGN KEY ("authorId") REFERENCES "TheLastUser" ("id") ON DELETE SET DEFAULT
);

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

sqlite3 CascadingDeletesDemo.db < cascading-deletes-set-default.sql

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

The deletion behavior SET NULL in this case means that when you delete a TheLastUser record that's referenced by one or more TheLastPost records, the authorId column on these TheLastPost records will be set to the default value of the column.

If no default exists, it will 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 will 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 can 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.

7. Introspect your database with Prisma

In the previous sections, you created five times two tables with foreign key constraints:

• The table Post uses RESTRICT action on the foreign key column authorId which points to the User table
• The table AnotherPost uses CASCADE action on the foreign key column authorId which points to the AnotherUser table
• The table OneMorePost uses NO ACTION action on the foreign key column authorId which points to the OneMoreUser table
• The table AlmostTheLastPost uses SET NULL action on the foreign key column authorId which points to the AlmostTheLastUser table
• The table TheLastPost uses SET DEFAULT action on the foreign key column authorId which points 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 called schema.prisma and add the following code to it:

schema.prisma
1datasource db {
2  provider = "sqlite"
3  url      = env("DATABASE_URL")
4}

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 called .env.

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

DATABASE_URL=file:CascadingDeletesDemo.db

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:

schema.prisma
1generator client {
2  provider = "prisma-client-js"
3}
4
5datasource db {
6  provider = "sqlite"
7  url      = env("DATABASE_URL")
8}
9
10model User {
11  id   Int     @default(autoincrement()) @id
12  name String?
13  Post Post[]
14}
15
16model Post {
17  authorId Int?
18  id       Int     @default(autoincrement()) @id
19  title    String?
20  User     User?   @relation(fields: [authorId], references: [id])
21}
22
23model AnotherUser {
24  id          Int           @default(autoincrement()) @id
25  name        String?
26  AnotherPost AnotherPost[]
27}
28
29model AnotherPost {
30  authorId    Int?
31  id          Int          @default(autoincrement()) @id
32  title       String?
33  AnotherUser AnotherUser? @relation(fields: [authorId], references: [id])
34}
35
36model OneMoreUser {
37  id          Int           @default(autoincrement()) @id
38  name        String?
39  OneMorePost OneMorePost[]
40}
41
42model OneMorePost {
43  authorId    Int?
44  id          Int          @default(autoincrement()) @id
45  title       String?
46  OneMoreUser OneMoreUser? @relation(fields: [authorId], references: [id])
47}
48
49model AlmostTheLastUser {
50  id                Int                 @default(autoincrement()) @id
51  name              String?
52  AlmostTheLastPost AlmostTheLastPost[]
53}
54
55model AlmostTheLastPost {
56  authorId          Int?
57  id                Int                @default(autoincrement()) @id
58  title             String?
59  AlmostTheLastUser AlmostTheLastUser? @relation(fields: [authorId], references: [id])
60}
61
62model TheLastUser {
63  id          Int           @default(autoincrement()) @id
64  name        String?
65  TheLastPost TheLastPost[]
66}
67
68model TheLastPost {
69  authorId    Int?         @default(42)
70  id          Int          @default(autoincrement()) @id
71  title       String?
72  TheLastUser TheLastUser? @relation(fields: [authorId], references: [id])
73}

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.

8. 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):

schema.prisma
1generator client {
2  provider = "prisma-client-js"
3}

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.

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

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

node index.js

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: QueryError(SqliteFailure(Error { code: ConstraintViolation, extended_code: 1811 }, Some("FOREIGN KEY constraint failed"))) })
    at PrismaClientFetcher.request (/Users/janedoe/cascading-deletes-demo/node_modules/@prisma/client/runtime/index.js:1:51487)
    at processTicksAndRejections (internal/process/task_queues.js:85:5)

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

node index.js

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

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

const prisma = new PrismaClient()

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

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

main()

node index.js

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

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

Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: ForeignKeyConstraintViolation { constraint: ForeignKey } })
    at PrismaClientFetcher.request (/Users/janedoe/cascading-deletes-demo/node_modules/@prisma/client/index.js:89:17)
    at process._tickCallback (internal/process/next_tick.js:68:7)

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

node index.js

$ node index.js
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}

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

const prisma = new PrismaClient()

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

  const postBefore = await prisma.theLastPost.findUnique({
    where: { id: userWithPost.TheLastPost[0].id },
    include: { TheLastUser: true },
  })

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

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

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

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

main()

node index.js

Before deleting the `TheLastUser` record, the `TheLastPost` record looks as follows:
{"authorId":1,"id":1,"title":"Hello World","TheLastUser":{"id":1,"name":"Alice"}}
(node:12668) UnhandledPromiseRejectionWarning: Error:
Invalid `deletedUser = await prisma.theLastUser.delete()` invocation in
/Users/janedoe/cascading-deletes-demo/index.js:25:54

  25     postBefore
  26   )}`
  27 );
  28
→ 29 const deletedUser = await prisma.theLastUser.delete(

Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: ForeignKeyConstraintViolation { constraint: ForeignKey } })
    at PrismaClientFetcher.request (/Users/janedoe/cascading-deletes-demo/node_modules/@prisma/client/index.js:89:17)
    at process._tickCallback (internal/process/next_tick.js:68:7)
(node:3074) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1)
(node:3074) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

$ node index.js
Before deleting the `TheLastUser` record, the `TheLastPost` record looks as follows:
{"authorId":43,"id":2,"title":"Hello World","TheLastUser":{"id":43,"name":"Alice"}}
After deleting the `TheLastUser` record, the `TheLastPost` record looks as follows:
{"authorId":42,"id":2,"title":"Hello World","TheLastUser":{"id":42,"name":null}}