Prisma: No way to cascade delete when the foreign key is non-nullable

[ranjan-purbey]

Bug description

1. When the annotated relation field in schema.prisma is made mandatory (no ? type suffix), running the migration automatically sets cascade deletion. When it is non-mandatory, it sets ON DELETE SET NULL. There should be a way to set the behavior in both cases.

2. Even when using introspection, when the "one" side of the relation is made non-nullable, deleting a record on this side causes prisma query engine to throw a relation violation error. Cascade delete works when the "one" side is nullable

How to reproduce

Steps to reproduce the behavior:

1. Create a SQLite db and run following SQL statements

DROP TABLE IF EXISTS User;
DROP TABLE IF EXISTS Post;


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

CREATE TABLE "Post" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "authorId" INTEGER NOT NULL,
  FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE CASCADE
);

INSERT INTO User("name") VALUES("Margot");
INSERT INTO Post ("authorId") VALUES(1);

1. Introspect the database and generate client JS
2. Run new PrismaClient().user.delete({where: {id: 1}})
3. See RelationViolation error
4. Make the "authorId" field nullable and re-introspect the db. Try deleting again
5. It works

Expected behavior

1. The cascade delete should work even when the foeign key field is non nullable
2. @relation directive should have onDelete and onUpdate flags

Environment & setup

• OS: Ubuntu 19.10
• Database: SQLite 3.30.1
• Prisma version: 2.0.0-beta.1
• Node.js version: v13.7.0

[pantharshit00]

I can confirm this. I think this is one the assumptions that query engine makes right now but I am not certain.

[robmurtagh]

Yes, I have exactly the same issue. I am running prisma:2.0.0-beta.1, and mysql:5.7.14 and have:

CREATE TABLE `platform`.`entity_basket_item` (
  ...
  FOREIGN KEY (`entity_basket_id`) REFERENCES `platform`.`entity_basket` (`id`) ON DELETE CASCADE
)

If the Foreign Key (basket, basketId) is non-nullable in Prisma schema (which is true in the DB schema):

model EntityBasket {
  entityBasketId Int                @default(autoincrement()) @id @map("id")
  items          EntityBasketItem[]

  @@map("entity_basket")
}

model EntityBasketItem {
  entityBasketItemId Int          @default(autoincrement()) @id @map("id")
  basket             EntityBasket @relation(fields: [basketId], references: [entityBasketId])
  basketId           Int          @map("entity_basket_id")

  @@map("entity_basket_item")
}

Then I receive error:

Error occurred during query execution:
InterpretationError("Error for binding \'3\': RelationViolation(RelationViolation { relation_name: \"EntityBasketToEntityBasketItem\", model_a_name: \"EntityBasket\", model_b_name: \"EntityBasketItem\" })")

However making the Foreign Key nullable (basket?, basketId?) fixes the error and the cascading delete works as expected:

model EntityBasket {
  entityBasketId Int                @default(autoincrement()) @id @map("id")
  items          EntityBasketItem[]

  @@map("entity_basket")
}

model EntityBasketItem {
  entityBasketItemId Int           @default(autoincrement()) @id @map("id")
  basket             EntityBasket? @relation(fields: [basketId], references: [entityBasketId])
  basketId           Int?          @map("entity_basket_id")

  @@map("entity_basket_item")
}

[ranjan-purbey]

@robmurtagh By "fixes the error" do you mean cascade delete works?

[robmurtagh]

Yes, exactly, cascade delete then works as expected.

[ranjan-purbey]

Thanks @robmurtagh for the confirmation. Last time I tried doing that with beta-1, it didn't work for me. I'll try again. Do you mind sharing your prisma version?

[robmurtagh]

Yes, I'm on 2.0.0-beta.1, and mysql:5.7.14

[ranjan-purbey]

Hey @robmurtagh I tried your solution and it works. But ideally it should also work when the foreign key is non-nullable.

[dodas]

This one can be probably closed now as well.

[robmurtagh]

Does that mean that the issue is fixed? Amazing news if so 😄

[ranjan-purbey]

This one can be probably closed now as well.

@dodas
I haven't tested the latest release. Can you please confirm whether cascade-delete works for non-nullable foreign-keys as well? We can close the issue if that is the case.

[kennytraction]

This one can be probably closed now as well.

@dodas
I haven't tested the latest release. Can you please confirm whether cascade-delete works for non-nullable foreign-keys as well? We can close the issue if that is the case.

I am running beta 6 and the issue doesn't seem to be fixed. I would guess that the closed linked issue was included in the release notes by accident.

[janpio]

Release notes unfortunately can not differentiate between issues that were closed as a duplicate vs. really fixed.

[Sytten]

You could add a tag for duplicate and exclude those issues.

[aakashkag]

Hello Prisma Team. Thanks for great product. Is it possible to fix this issue in priority. it's very importent feature in any project. Still waiting from last 3 release. Please. ^^

[MikaStark]

I don’t understand why cascade deletion is not supported... there are a bunch of workaround on the website for each Db kind and it seems to be just adding constraints ON DELETE but why this can’t be automatic ? (Just like prisma1 with decorators). This is a very important issue I think.

[aakashkag]

Hope a day will come when this issue will solve by awesome Prisma team. :)

[jwld]

Any chance we could get a timeline on this fix? Is it days, weeks or months away? It's a pretty painful bug that's preventing us from properly using prisma migrate.

Just to explain, this is our current workflow:

• Set all required relations as non-required in the datamodel, with comments to remind us to set back to required once this bug is fixed
• Run prisma migrate and generate a client that allows for things to be deleted
• Run a script to manually set all these relations to cascading delete on the DB level
• _Any_ time we run prisma migrate it's deleting those manually-set cascading deletes (since the relation is not required), so we have to re-run that "post-migration" script to set those cascading deletes on the DB level again - if we forget then nothing cascades

[P4sca1]

A timeline on when we can specify cascade deletes / updates in the prisma schema would also be great.

[janpio]

This issue is _not_ about defining cascading deletes in the Prisma Schema, but about the inability to manually use cascade deletes (by defining them manually) when the relation field is not defined as optional (non-nullable).

Surprisingly we did not have a clear one for that, so I created one: https://github.com/prisma/prisma/issues/2810

[jhanschoo]

Note that the workaround by defining the foreign keys to be optional becomes problematic in 2.2.0 of @prisma/cli and @prisma/client in an important use case of implicit many-to-many tables. These tables require the @@id([A, B]) annotation, but 2.2.0's validation checker enforces that neither A nor B are optional when they appear in the annotation.

edit: fixed unreadable run-on sentence, I was about to go to bed when I originally wrote this.

[jwld]

@jhanschoo How are you getting round this? Just using a raw SQL query instead?

[jhanschoo]

@jwld I'm pinning to 2.1.3 since I don't need the aggregateApi of 2.2.x just yet.

[pbelbin]

Is this issue being worked on? It's a huge pain, and seems like it's a bug that should be fixed by now.

[nikolasburk]

This issue depends on the implementation of cascading deletes: https://github.com/prisma/prisma/issues/2810

Unfortunately, cascading deletes are not actively being worked on _right now_ but they're already on the roadmap and will likely be picked up by the engineering team soon!

In the meantime, I want to mention that @AhmedElywa has built an awesome library that brings you cascading deletes to Prisma already today: https://paljs.com/plugins/delete

You can use it as a workaround for the time where it's not yet natively supported by Prisma.

[Sytten]

Ha funny that I didnt find it before, related issue: https://github.com/prisma/prisma/issues/4650