Null constraint violation with referential actions

[tlindener]

Bug description

After upgrading to 2.27.0 I also started using the referential actions. In my e2e tests i delete all entities based on a tenantId (multi-tenant setup)

Now when using prisma.locations.deleteMany({where: {tenantId: "myTenantName"}}) I get:

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

I checked the new migration file and the "Location" models foreign keys have not been touched (no change to the initial migration which is as expected).

The reason is with the SetNull prisma decides to set both ids to null which does not work for the model "Data" because tenantId is itself part of the composite primary key.

How to reproduce

Use provided model and this script:

import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
async function main() {
    const tenant = await prisma.tenant.create({
        data: {
            id: "myId"
        }
    })
    await prisma.location.create({
        data: {
            id: "myId",
            tenantId: tenant.id,
            data: {
                create: {
                    id: "myId",
                }
            }
        },

    })
    await prisma.location.deleteMany({
        where: {
            tenantId: tenant.id
        }
    })
}
main()
    .catch(e => {
        throw e
    })
    .finally(async () => {
        await prisma.$disconnect()
    })

Expected behavior

Should set only the optional Id to null and ignore the tenantId since it's part of the composite primary key.

Prisma information

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

datasource db {
    provider = "postgresql"
    url      = "postgresql://admin:admin@localhost:5432/test?schema=public"
}

model Data {
    tenantId   String    @db.VarChar
    id         String    @db.VarChar
    locationId String?   @db.VarChar
    location   Location? @relation(fields: [tenantId, locationId], references: [tenantId, id], onDelete: SetNull)

    @@id([tenantId, id])
}

model Location {
    tenantId String                     @db.VarChar
    id       String                     @db.VarChar
    data     Data?
    tenant   Tenant                     @relation(fields: [tenantId], references: [id], onDelete: Cascade)

    @@id([tenantId, id])
}


model Tenant {
    id            String          @id @db.VarChar
    locations     Location[]
}

Environment & setup

• OS: Windows
• Database: PostgreSQL
• Node.js version: v16.4.0

Prisma Version

prisma               : 2.27.0
@prisma/client       : 2.27.0
Current platform     : windows
Query Engine         : query-engine cdba6ec525e0213cce26f8e4bb23cf556d1479bb (at node_modules\@prisma\engines\query-engine-windows.exe)
Migration Engine     : migration-engine-cli cdba6ec525e0213cce26f8e4bb23cf556d1479bb (at node_modules\@prisma\engines\migration-engine-windows.exe)
Introspection Engine : introspection-core cdba6ec525e0213cce26f8e4bb23cf556d1479bb (at node_modules\@prisma\engines\introspection-engine-windows.exe)
Format Binary        : prisma-fmt cdba6ec525e0213cce26f8e4bb23cf556d1479bb (at node_modules\@prisma\engines\prisma-fmt-windows.exe)
Default Engines Hash : cdba6ec525e0213cce26f8e4bb23cf556d1479bb
Studio               : 0.410.0
Preview Features     : referentialActions

[janpio]

Should set only the optional Id to null and ignore the tenantId since it's part of the composite primary key.

I don't think Prisma is doing anything here. Referential actions live on a database level and are only configured in Prisma Schema. When you migrate your database, the generated SQL is executed and when you run a query later, that SQL just becomes active and does what it is configured to do.

In your example:

This is the logging you get when you have it all enabled (https://www.prisma.io/docs/concepts/components/prisma-client/working-with-prismaclient/logging + https://www.prisma.io/docs/concepts/components/prisma-client/debugging/):

  prisma:client Prisma Client call: +260ms
  prisma:client prisma.location.deleteMany({
  prisma:client   where: {
  prisma:client     tenantId: 'myId'
  prisma:client   }
  prisma:client }) +1ms
  prisma:client Generated request: +1ms
  prisma:client mutation {
  prisma:client   deleteManyLocation(where: {
  prisma:client     tenantId: "myId"
  prisma:client   }) {
  prisma:client     count
  prisma:client   }
  prisma:client }
  prisma:client  +1ms
  prisma:engine stdout Fetched a connection from the pool +16ms
  prisma:engine stdout Unknown error +32ms
prisma:query BEGIN
  prisma:engine stdout Unknown error +48ms
prisma:query SELECT "public"."Location"."tenantId", "public"."Location"."id" FROM "public"."Location" WHERE "public"."Location"."tenantId" = $1 OFFSET $2
  prisma:engine Client Version: 2.27.0 +32ms
  prisma:engine Engine Version: query-engine cdba6ec525e0213cce26f8e4bb23cf556d1479bb +1ms
  prisma:engine Active provider: postgresql +0ms
  prisma:engine stdout Unknown error +5ms
prisma:query SELECT "public"."Location"."tenantId", "public"."Location"."id" FROM "public"."Location" WHERE "public"."Location"."tenantId" = $1
  prisma:engine stdout Unknown error +42ms
prisma:query DELETE FROM "public"."Location" WHERE ("public"."Location"."tenantId","public"."Location"."id") IN (($1,$2))
  prisma:engine stdout Unknown error +19ms
prisma:query ROLLBACK
  prisma:client:fetcher Error: Null constraint violation on the fields: (`tenantId`)
  prisma:client:fetcher     at BinaryEngine.graphQLToJSError (C:\Users\Jan\Documents\throwaway\repro8264\node_modules\@prisma\client\runtime\index.js:28964:16)
  prisma:client:fetcher     at BinaryEngine.request (C:\Users\Jan\Documents\throwaway\repro8264\node_modules\@prisma\client\runtime\index.js:28862:24)
  prisma:client:fetcher     at processTicksAndRejections (internal/process/task_queues.js:93:5)
  prisma:client:fetcher     at async cb (C:\Users\Jan\Documents\throwaway\repro8264\node_modules\@prisma\client\runtime\index.js:34770:26)
  prisma:client:fetcher     at async main (C:\Users\Jan\Documents\throwaway\repro8264\script.js:23:5) +0ms
  prisma:engine Stopping Prisma engine4 +11ms
  prisma:engine Waiting for start promise +1ms
  prisma:engine Done waiting for start promise +1ms
(node:3596) UnhandledPromiseRejectionWarning: Error:
Invalid `prisma.location.deleteMany()` invocation in
C:\Users\Jan\Documents\throwaway\repro8264\script.js:23:27

  20     },
  21
  22 })
→ 23 await prisma.location.deleteMany(
  Null constraint violation on the fields: (`tenantId`)
    at cb (C:\Users\Jan\Documents\throwaway\repro8264\node_modules\@prisma\client\runtime\index.js:34800:17)
    at processTicksAndRejections (internal/process/task_queues.js:93:5)
    at async main (C:\Users\Jan\Documents\throwaway\repro8264\script.js:23:5)
(Use `node --trace-warnings ...` to show where the warning was created)
(node:3596) 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(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 1)
(node:3596) [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 c

As you can see it basically tries to run a DELETE FROM "public"."Location" WHERE ("public"."Location"."tenantId","public"."Location"."id") IN (($1,$2)) which then is declined by the database with Error: Null constraint violation on the fields: (tenantId).

[janpio]

Here is the SQL of the database for context:

-- CreateTable
CREATE TABLE "Data" (
    "tenantId" VARCHAR NOT NULL,
    "id" VARCHAR NOT NULL,
    "locationId" VARCHAR,

    PRIMARY KEY ("tenantId","id")
);

-- CreateTable
CREATE TABLE "Location" (
    "tenantId" VARCHAR NOT NULL,
    "id" VARCHAR NOT NULL,

    PRIMARY KEY ("tenantId","id")
);

-- CreateTable
CREATE TABLE "Tenant" (
    "id" VARCHAR NOT NULL,

    PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "Data_tenantId_locationId_unique" ON "Data"("tenantId", "locationId");

-- AddForeignKey
ALTER TABLE "Data" ADD FOREIGN KEY ("tenantId", "locationId") REFERENCES "Location"("tenantId", "id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "Location" ADD FOREIGN KEY ("tenantId") REFERENCES "Tenant"("id") ON DELETE CASCADE ON UPDATE CASCADE;

[tlindener]

Yes I agree Prisma is not doing anything which isn't expected base on what I can set in the prisma schema.
Then again, I would hope to be able to do something like
location Location? @relation(fields: [tenantId, locationId], references: [tenantId, id], onDelete: [SetNull(id)]
to tell prisma migrate to ignore the tenantId.

In other words, I would like the prisma schema to allow me being specific about the intent of the referential actions.

[pantharshit00]

I think this would be a feature that we will need to add to the schema in order to enable this.

cc @tomhoule

Workaround would be manually modify the migration which migrate allows.

[thebiglabasky]

Ah yes, that's something that isn't supported at the database level though.
Having something like what is proposed here would introduce a mix-up: either it's SetNull and gets transcribed into a database-level constraint (ON DELETE SET NULL) or it is something like SetNul(id) and that has to be implemented at Prisma-level. This would introduce discrepancies that might cause trouble.

We should ensure this is documented properly, and advise to use a trigger-like workaround for now.

EDIT: Docs already mention the limitation here: https://www.prisma.io/docs/concepts/components/prisma-schema/relations/referential-actions#setnull