Delete of "parent" should not be prevented if it will cascade delete "children" on required relations

[Sytten]

Bug description

Consider the simplified schema:

model Calendar {
  id                   String                        @id @default(cuid())
  events          CalendarEvent[]
}

model CalendarEvent {
  calendar     Calendar @relation(fields: [calendarId], references: [id])
  calendarId   String
  id           String   @id @default(cuid())
}

When trying to delete calendar I get:

The change you are trying to make would violate the required relation 'CalendarToCalendarEvent' between the `Calendar` and `CalendarEvent` models.
    at PrismaClientFetcher.request (/Users/<REDACTED>/node_modules/@prisma/client/runtime/index.js:78121:15)
    at processTicksAndRejections (internal/process/task_queues.js:94:5) {
  code: 'P2014',
  clientVersion: '2.13.0',
  meta: {
    relation_name: 'CalendarToCalendarEvent',
    model_a_name: 'Calendar',
    model_b_name: 'CalendarEvent'
  },

This is contrary to what the doc says on the matter https://www.prisma.io/docs/guides/general-guides/database-workflows/cascading-deletes/postgresql

How to reproduce

1. Create the schema above
2. Use the "old" migration system to generate a migration
3. Apply said migration
4. Put some rows in both tables
5. Try to delete calendar, you get the error

Expected behavior

You should be allowed to delete the calendar and let the DB delete the children objects.
This is a somewhat new behaviour because I remember doing that successfully in a previous version.

Prisma information

I tested on Postgres, but probably is also valid for other databases.

@prisma/cli          : 2.13.0
@prisma/client       : 2.13.0
Current platform     : darwin
Query Engine         : query-engine 833ab05d2a20e822f6736a39a27de4fc8f6b3e49 (at ../node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli 833ab05d2a20e822f6736a39a27de4fc8f6b3e49 (at ../node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core 833ab05d2a20e822f6736a39a27de4fc8f6b3e49 (at ../node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt 833ab05d2a20e822f6736a39a27de4fc8f6b3e49 (at ../node_modules/@prisma/engines/prisma-fmt-darwin)
Studio               : 0.329.0

[Sytten]

@pantharshit00 if you have time to create a repro 🙏

[Sytten]

So I wrote a repro here: https://github.com/Sytten/repro-prisma-4650
The issue is that the relation calendar Calendar @relation(fields: [calendarId], references: [id]) is not optional contrary to the example in the documentation. This is correct since it is not optional in the DB but this prevents the engine from deleting the calendar record even if it is a legit operation.

[pantharshit00]

Hey, Thanks for the reproduction here. I can confirm this. I also made a reproduction for this yesterday but forgot to post it, you did a better one here today so we will use that. Thanks again :)

[Sytten]

The work around for now is using paljs plugin Delete: https://paljs.com/plugins/delete/

[matthewmueller]

Thanks for the very helpful reproduction @Sytten! You can workaround this by making the Calendar optional:

model Calendar {
  id                   String                        @id @default(cuid())
  events          CalendarEvent[]
}

model CalendarEvent {
- calendar     Calendar @relation(fields: [calendarId], references: [id])
+ calendar     Calendar? @relation(fields: [calendarId], references: [id])
  calendarId   String
  id           String   @id @default(cuid())
}

We have a guide that discusses this in more detail: https://www.prisma.io/docs/guides/general-guides/database-workflows/cascading-deletes/postgresql

However, I don't think the cascade settings shouldn't depend on optionality. I'll talk to the team about fixing this up.

[Sytten]

@matthewmueller Looking at the queries it does, it checks if any relation would be affected. I our case it runs 6 queries before the delete so this very wasteful and not necessary since the DB already ensure integrity with the cascade setting.

[matthewmueller]

Looks like this one is unfortunately blocked by #2810. We have this check to maintain data integrity when you have a required relation without cascade delete on the underlying database. It will also be needed for Mongo soon.

Since we don't have state in the schema about the database's cascade settings, we can't turn this check on and off accordingly. We'll revisit this one after #2810.

In the meantime, optional relations or Ahmed's paljs plugin is the way to go. Sorry about that!

[pimeys]

This should be now possible to do with 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 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