Unable to delete Obj1 if Obj2 references it with Action: SetNull

[baconcheese113]

Bug description

When using the Action: SetNull referential action for a model relationship the deletion fails and throws an error about violating a foreign key constraint:

Invalid `prisma.hub.delete()` invocation:\n\n\n  Foreign key constraint failed on the field: `BatteryLevel_hubId_fkey (index)`

When I update the set the field with the foreign key to be null ahead of the deletion it works:

await prisma.batteryLevel.updateMany({ where: { hubId: id }, data: { hubId: null } })

How to reproduce

1. Use the Action: SetNull Cascade option for a model relationship
2. Create a field of both objects
3. Try to delete the parent object

Expected behavior

The foreign key field should be set to null and the deletion should succeed

Prisma information

model Hub {
  id                    Int                    @id @default(autoincrement())
  createdAt             DateTime               @default(now())
  updatedAt             DateTime               @default(now()) @updatedAt
  name                  String
  batteryLevels         BatteryLevel[]
}
model BatteryLevel {
  id        Int      @id @default(autoincrement())
  volts     Float
  percent   Float
  hubId     Int?
  hub       Hub?     @relation(fields: [hubId], references: [id], onDelete: SetNull)
  createdAt DateTime @default(now())
}

Environment & setup

• OS: Windows 10
• Database: PostgreSQL
• Node.js version: v16.14.2

Prisma Version

prisma                  : 3.15.2
@prisma/client          : 3.15.2
Current platform        : windows
Query Engine (Node-API) : libquery-engine 461d6a05159055555eb7dfb337c9fb271cbd4d7e (at node_modules\@prisma\engines\query_engine-windows.dll.node)
Migration Engine        : migration-engine-cli 461d6a05159055555eb7dfb337c9fb271cbd4d7e (at node_modules\@prisma\engines\migration-engine-windows.exe)
Introspection Engine    : introspection-core 461d6a05159055555eb7dfb337c9fb271cbd4d7e (at node_modules\@prisma\engines\introspection-engine-windows.exe)
Format Binary           : prisma-fmt 461d6a05159055555eb7dfb337c9fb271cbd4d7e (at node_modules\@prisma\engines\prisma-fmt-windows.exe)
Default Engines Hash    : 461d6a05159055555eb7dfb337c9fb271cbd4d7e
Studio                  : 0.462.0

[matthewmueller]

Can you share your full Prisma Schema including datasource and generator? We're wondering what your referentialIntegrity settings are.

[baconcheese113]

Pretty minimal, you can see the schema/datasource/generator here and the mutation here

[jkomyno]

Hi, could you please try again with Prisma 4.1.0? We tried reproducing this issue, but we observed the proper behavior: deleting 1 hub row results in setting all the related batteryLevel's hubIds to NULL.

Could you also please try running prisma db pull --force? We suspect the onDelete: SetNull might have been added after pushing your schema to the database, hence Postgres might not be aware of it.

[baconcheese113]

Interesting, so when I did prisma db pull --force the schema reflected not having any Cascade option set, yet my BatteryLevel_hubid_fkey is set up as

[janpio]

That is unfortunate. Can you maybe share the CREATE TABLE/DDL for this table via DBeaver? (You should be able to right click the table name in the schema view, and then choose Generate SQL -> DDL)

[baconcheese113]

-- public."BatteryLevel" definition

-- Drop table

-- DROP TABLE public."BatteryLevel";

CREATE TABLE public."BatteryLevel" (
	id serial4 NOT NULL,
	"hubId" int4 NULL,
	"createdAt" timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
	volts float8 NOT NULL,
	"percent" float8 NOT NULL,
	CONSTRAINT "BatteryLevel_pkey" PRIMARY KEY (id)
);


-- public."BatteryLevel" foreign keys

ALTER TABLE public."BatteryLevel" ADD CONSTRAINT "BatteryLevel_hubId_fkey" FOREIGN KEY ("hubId") REFERENCES public."Hub"(id) ON DELETE SET NULL ON UPDATE CASCADE;

[jkomyno]

I'm currently re-working on a reproduction of this issue, but the fact that no onUpdate: Cascade is reflected in the schema is expected.
In fact, the default referential action for update rules is Cascade, and Prisma introspection doesn't render default values in the schema.prisma file.

From the docs:

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.

[jkomyno]

After trying to reproduce this issue with prisma@3.15.2, prisma@4.2.0, Postgres 10, Postgres 14, I confirm that we're not observing any anomalous behavior. May you please share a minimal reproduction like the following so we can follow along?

Schema

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

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

model Hub {
  id                    Int                    @id @default(autoincrement())
  name                  String
  batteryLevels         BatteryLevel[]
}

model BatteryLevel {
  id        Int      @id @default(autoincrement())
  hubId     Int?

  // recall that `onUpdate: Cascade` is implicit
  hub       Hub?     @relation(fields: [hubId], references: [id], onDelete: SetNull)
}

Reproduction

// 'hub-1' has batteryLevels with ids in [1, 2]
await prisma.hub.create({
  data: {
    id: 1,
    name: 'hub-1',
    batteryLevels: {
      createMany: {
        data: [
          { id: 1 },
          { id: 2 },
        ]
      }
    }
  },
})

// 'hub-2' has batteryLevels with ids in [3, 4]
await prisma.hub.create({
  data: {
    id: 2,
    name: 'hub-2',
    batteryLevels: {
      createMany: {
        data: [
          { id: 3 },
          { id: 4 },
        ]
      }
    }
  },
})

// we get rid of 'hub-2'
await prisma.hub.delete({ where: { id: 2 } })

const hubs = await prisma.hub.findMany({
  include: { batteryLevels: true },
  orderBy: { id: 'asc' },
})
expect(hubs).toMatchObject([
  id: 1,
  name: 'hub-1',
  batteryLevels: [ { id: 1, hubId: 1 }, { id: 2, hubId: 1 } ],
])

// the batteryLevels that were linked to 'hub-2' have `hubId: null` now
const batteryLevels = await prisma.batteryLevel.findMany({ orderBy: { id: 'asc' } })
expect(batteryLevels).toMatchObject([
  { id: 1, hubId: 1 },
  { id: 2, hubId: 1 },
  { id: 3, hubId: null },
  { id: 4, hubId: null }
])

[baconcheese113]

After upgrading to Prisma 4.2.1 and migrating from Nexus + Nexus-prisma-plugin to Pothos, and Node version 18.7.0 I'm no longer experiencing this issue 👍 Appreciate all the assistance!

[janpio]

Thanks for the update @baconcheese113. If anyone ever hits this issue again, please let us know via a comment here or open a new one. Thanks!