Client: In 1:1,1:n, m:n relations using OnDelete: SetNull with referentialIntegrity = "prisma" user.delete() should fail, but succeeds.

[Jolg42]

Bug description

I expected that the Prisma Client emulation (triggered by referentialIntegrity="prisma") would behave like without for Prisma Client queries, but the following queries in the script succeed instead of failing.

Note: Removing onDelete: SetNull and trying the same script errors with

 The change you are trying to make would violate the required relation 'ProfileOneToOneToUserOneToOne' between the `ProfileOneToOne` and `UserOneToOne` models.

How to reproduce

• npx prisma db push --force-reset
• ts-node main.ts
• See tables logs

Expected behavior

Our referentialIntegrity emulation in Prisma Client of onUpdate: SetNull, onDelete: SetNull should do the same as what happens on SQL databases using Foreign Keys.

On all supported SQL databases, this .delete() throws, and we get the following database error

Null constraint violation on the fields: (`userId`)

Prisma information

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["referentialIntegrity"]
}

datasource db {
  provider             = "postgresql"
  url                  = "postgresql://prisma:prisma@localhost:5432/14759"
  referentialIntegrity = "prisma"
}

//
// 1:1 relation
//
model UserOneToOne {
  id      String           @id
  profile ProfileOneToOne?
  enabled Boolean?
}

model ProfileOneToOne {
  id      String       @id
  user    UserOneToOne @relation(fields: [userId], references: [id], onUpdate: SetNull, onDelete: SetNull)
  userId  String       @unique
  enabled Boolean?
}

//
// 1:n relation
//
model UserOneToMany {
  id      String          @id
  posts   PostOneToMany[]
  enabled Boolean?
}

model PostOneToMany {
  id       String        @id
  author   UserOneToMany @relation(fields: [authorId], references: [id], onUpdate: SetNull, onDelete: SetNull)
  authorId String
}

//
// m:n relation
//

model PostManyToMany {
  id         String                        @id
  categories CategoriesOnPostsManyToMany[]
  published  Boolean?
}

model CategoryManyToMany {
  id        String                        @id
  posts     CategoriesOnPostsManyToMany[]
  published Boolean?
}

model CategoriesOnPostsManyToMany {
  post       PostManyToMany     @relation(fields: [postId], references: [id], onUpdate: SetNull, onDelete: SetNull)
  postId     String
  category   CategoryManyToMany @relation(fields: [categoryId], references: [id], onUpdate: SetNull, onDelete: SetNull)
  categoryId String

  @@id([postId, categoryId])
}

import { PrismaClient } from "@prisma/client";

async function main() {
  const prisma = new PrismaClient();

  // 1:1 relation
  async function createXUsersWithAProfile({
    count,
    profileColumn,
  }: {
    count: number;
    profileColumn: string;
  }) {
    const prismaPromises: any = [];

    for (let i = 0; i < count; i++) {
      // We want to start at 1
      const id = (i + 1).toString();
      const prismaPromise = prisma.userOneToOne.create({
        data: {
          id: id,
          [profileColumn]: {
            create: { id: id },
          },
        },
        include: {
          [profileColumn]: true,
        },
      });
      prismaPromises.push(prismaPromise);
    }

    return await prisma.$transaction(prismaPromises);
  }

  // 1:n relation
  async function createXUsersWith2Posts({ count }: { count: number }) {
    const prismaPromises = [] as Array<Promise<any>>;

    for (let i = 1; i <= count; i++) {
      const id = i.toString();

      prismaPromises.push(
        prisma.userOneToMany.create({
          data: {
            id,
          },
        })
      );

      prismaPromises.push(
        prisma.postOneToMany.create({
          data: {
            id: `${id}-post-a`,
            authorId: id,
          },
        })
      );

      prismaPromises.push(
        prisma.postOneToMany.create({
          data: {
            id: `${id}-post-b`,
            authorId: id,
          },
        })
      );
    }

    // @ts-ignore
    return await prisma.$transaction(prismaPromises);
  }

  // m:n relation (SQL database)
  async function createXPostsWith2CategoriesSQLDb({
    count,
  }: {
    count: number;
  }) {
    const prismaPromises: any = [];

    for (let i = 0; i < count; i++) {
      // We want to start at 1
      const id = (i + 1).toString();
      const prismaPromise = prisma.postManyToMany.create({
        data: {
          id: id,
          categories: {
            create: [
              {
                category: {
                  create: {
                    id: `${id}-cat-a`,
                  },
                },
              },
              {
                category: {
                  create: {
                    id: `${id}-cat-b`,
                  },
                },
              },
            ],
          },
        },
        include: {
          categories: true,
        },
      });
      prismaPromises.push(prismaPromise);
    }

    return await prisma.$transaction(prismaPromises);
  }

  await prisma.$transaction([
    prisma.profileOneToOne.deleteMany(),
    prisma.userOneToOne.deleteMany(),

    prisma.userOneToMany.deleteMany(),
    prisma.postOneToMany.deleteMany(),

    prisma.categoriesOnPostsManyToMany.deleteMany(),
    prisma.postManyToMany.deleteMany(),
    prisma.categoryManyToMany.deleteMany(),
  ]);

  //
  console.info("\n- 1:1 relation -\n");
  //

  await createXUsersWithAProfile({
    count: 2,
    profileColumn: "profile",
  });

  // Deleting the user, deletes the user and the profile is untouched
  const deletedUser1to1 = await prisma.userOneToOne.delete({
    where: { id: "1" },
  });
  console.info("\nUser was deleted, no error was thrown");
  console.log({ deletedUser1to1 });
  console.log();

  const userOneToOne = await prisma.userOneToOne.findMany({});
  console.log("userOneToOne");
  console.table(userOneToOne);

  const profileOneToOne = await prisma.profileOneToOne.findMany({});
  console.log("profileOneToOne");
  console.table(profileOneToOne);

  //
  console.info("\n- 1:n relation -\n");
  //

  await createXUsersWith2Posts({
    count: 2,
  });

  // Deleting the user, deletes the user and the profile is untouched
  const deletedUser1ton = await prisma.userOneToMany.delete({
    where: { id: "1" },
  });
  console.info("\nUser was deleted, no error was thrown");
  console.log({ deletedUser1ton });
  console.log();

  const userOneToMany = await prisma.userOneToMany.findMany({});
  console.log("userOneToMany");
  console.table(userOneToMany);

  const postOneToMany = await prisma.postOneToMany.findMany({});
  console.log("postOneToMany");
  console.table(postOneToMany);

  //
  console.info("\n- m:n relation -\n");
  //

  await createXPostsWith2CategoriesSQLDb({
    count: 4,
  });

  const deletedpostmton = await prisma.postManyToMany.delete({
    where: { id: "1" },
  });
  console.info("\nPost was deleted, no error was thrown");
  console.log({ deletedpostmton });

  const deletedcategorymton = await prisma.categoryManyToMany.delete({
    where: { id: "1-cat-a" },
  });
  console.info("\nCategory was deleted, no error was thrown");
  console.log({ deletedcategorymton });

  const updatedpostmton = await prisma.postManyToMany.update({
    where: {
      id: "3",
    },
    data: {
      id: "99",
    },
  });
  console.info("\nupdatedpostmton was updated, no error was thrown");
  console.log({ updatedpostmton });

  const updatedcategorymton = await prisma.categoryManyToMany.update({
    where: {
      id: "4-cat-a",
    },
    data: {
      id: "4-cat-a-updated",
    },
  });
  console.info("\nupdatedcategorymton was updated, no error was thrown");
  console.log({ updatedcategorymton });
  console.log();

  const categoryManyToMany = await prisma.categoryManyToMany.findMany({});
  console.log("categoryManyToMany");
  console.table(categoryManyToMany);

  const postManyToMany = await prisma.postManyToMany.findMany({});
  console.log("postManyToMany");
  console.table(postManyToMany);

  const categoriesOnPostsManyToMany =
    await prisma.categoriesOnPostsManyToMany.findMany({});
  console.log("categoriesOnPostsManyToMany");
  console.table(categoriesOnPostsManyToMany);

  prisma.$disconnect();
}

main();

Environment & setup

• OS: macOS
• Database: Any
• Node.js version: v16.16.0

Prisma Version

prisma                  : 4.4.0
@prisma/client          : 4.4.0
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine f352a33b70356f46311da8b00d83386dd9f145d6 (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli f352a33b70356f46311da8b00d83386dd9f145d6 (at node_modules/@prisma/engines/migration-engine-darwin-arm64)
Introspection Engine    : introspection-core f352a33b70356f46311da8b00d83386dd9f145d6 (at node_modules/@prisma/engines/introspection-engine-darwin-arm64)
Format Binary           : prisma-fmt f352a33b70356f46311da8b00d83386dd9f145d6 (at node_modules/@prisma/engines/prisma-fmt-darwin-arm64)
Format Wasm             : @prisma/prisma-fmt-wasm 4.4.0-66.f352a33b70356f46311da8b00d83386dd9f145d6
Default Engines Hash    : f352a33b70356f46311da8b00d83386dd9f145d6
Studio                  : 0.474.0

[Jolg42]

I updated the script and schema to show how this affects any relation with SetNull.

Also note how it affects .update() for the m:n relation as well.

[Jolg42]

We need to tighten the validation, see #14673

[jkomyno]

Closed as it's now solved.

[Jolg42]

This can't happen anymore as we have a schema validation