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

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

[nikolasburk]

This issue depends on the implementation of cascading deletes: #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: #4650

[satilog]

As a workaround, one could execute a raw sql statement through prisma so that the cascaded delete constraint set on the database would work as expected.

const result = await prisma.$executeRaw`DELETE FROM User WHERE id=${userId};`

https://www.prisma.io/docs/concepts/components/prisma-client/raw-database-access

[pbelbin]

Yes, and, that's what we're having to do, but, really, this sort of thing should not have to be worked around!!!

[vidz1979]

I think it's a better workaround to recreate foreign key the right way after applying Prisma migration:

ALTER TABLE "Reading" DROP CONSTRAINT "Reading_createdById_fkey"; // <--- Old contrainst: ON DELETE SET NULL

ALTER TABLE "Reading" ADD FOREIGN KEY ("createdById") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

[StarpTech]

What's the status? This is obviously an important bug. I'm trying prisma and run into that issue. ORM's 🤗

[MikaStark]

According to the roadmap : https://www.notion.so/Prisma-Roadmap-50766227b779464ab98899accb98295f

Prisma team is working on it

[jasonlimantoro]

Is this a priority at all for the Prisma team? seems like not 😢.

Prisma even has documented the following solutions for cascading deletes as if they were the correct solution all along (not as workarounds).
(https://www.prisma.io/docs/concepts/components/prisma-client/crud#cascading-deletes-deleting-related-records):

• Make relations optional
• Change the ownership relationships temporarily
• Manually deleting the child records first and then, batch it with prisma.$transaction along with the parent record.

[MikaStark]

Prisma team is working hard on a large bundle of tools like Prisma Studio or Prisma migrate. The latest was a mandatory step to achieve a stable and production-ready cascade delete.
Like any company in the world, human and technical resources are limited and cannot cover every issue instantly. And on top of that all Prisma features are free !

[albertoperdomo]

Hello from Prisma here! @jasonlimantoro

Thanks, @MikaStark - It's true! The team is actively working on this problem.

Right now, the potential design for this feature is being discussed and you can expect soon to see a proposal that will be shared with the community for broader feedback, before going into the implementation phase.

Hope this helps alleviate some concerns around the timing and prioritization of this.

[bencun]

I do like the current implementation with the deletion being forbidden on non-optional relations but that should be an optional kind of behaviour, the cascade deletes should work by default and we should then be able to optionally enforce strict no-cascade behaviour with a modifier on the relationship itself. That would be awesome.

[albertoperdomo]

Hello everyone and thanks for your patience!

We've spent some time brainstorming about problem, discussing possible solutions, and finally have a proposal to share. The idea is to extend the Prisma Schema Language in order to be able to encode ON DELETE and ON UPDATE behavior of the foreign keys.

Please find the proposal with all the relevant details in this GH issue. We are welcoming feedback at this stage and look forward to hearing from you!

[pimeys]

Fixed in prisma/prisma-engines#1947

Instructions on how to use this, and a place to give feedback: #7816

[janpio]

This has now been released as a preview feature behind a preview feature flag. You can read about it in the release notes for 2.26.0: https://github.com/prisma/prisma/releases/tag/2.26.0 If you have any feedback, please use this issue: #7816