In Prisma 4.6.0, changing onDelete: SetNull to Cascade results in recreating foreign key

[jameyhart]

Discussed in #16223

^{Originally posted by jameyhart November 10, 2022}
@janpio After upgrading to 4.6.0, we started reciving the following error when migrating our database:

Error: Schema validation error - Error (query-engine-node-api library)
Error code: P1012
error: Error parsing attribute "@relation": The `onDelete` referential action of a relation must not be set to `SetNull` when a referenced field is required.
Either choose another referential action, or make the referenced fields optional.

  -->  schema.prisma:126
   |
125 |     
126 |     survey    Survey   @relation(fields: [surveyId], references: [id], onDelete: SetNull)
127 |
   |

To rectify this, we changed the onDelete from SetNull to Cascade but it then cerates a new migration that drops the foreign key and recreates it.
Is this the expected behaviour?

Our current database model:

model Survey {
    id                      String                    @id @default(cuid())
    name                    String
    survey                  Json
    reportSurvey            SurveyReport[]
    ...
}

model SurveyReport {
    id                String       @id @default(cuid())
    surveyId          String
    survey            Survey       @relation(fields: [surveyId], references: [id], onDelete: SetNull)
    reportType        SurveyReportType
    ...
}

The output from the new migration:

-- DropForeignKey
ALTER TABLE "SurveyReport" DROP CONSTRAINT "SurveyReport_surveyId_fkey";

-- AddForeignKey
ALTER TABLE "SurveyReport" ADD CONSTRAINT "SurveyReport_surveyId_fkey" FOREIGN KEY ("surveyId") REFERENCES "Survey"("id") ON DELETE CASCADE ON UPDATE CASCADE;

[janpio]

Can you check if this is still happening with 4.6.1 that went out a short time ago?
I expect it to, but want to make sure. Thanks.

We tightened our validation rules around referential actions in 4.6.0: onDelete: SetNull is accepted by the database, but can never work as the foreign key relation surveyId is actually required / not options / not nullable - so the referential action would always fails. Did you write this schema by hand or is this an existing database?

If dropping and recreating vs. altering is correct I am trying to figure out internally.

[Jolg42]

Hi @jameyhart, thanks for opening this issue.

The SQL is correct, we are not aware of another way to change the referential action on PostgreSQL unfortunately, a drop and create must happen.

The validation error you see, like @janpio mentioned, is a recent change added in #14673 which is not yet documented (we will do that soon).
PostgreSQL allows the action to be SET NULL on a required field, which will error at runtime since NULL will be an invalid value. This validation error protects you from that runtime error.

• Did you write this schema by hand or is this an existing database?
• Does this make sense to you?

Let us know if something is unclear or if this is a problem for you.

[jameyhart]

Hi both, thanks for the speedy reply!

So, upgrading to v4.6.1 fixed the bug we were experiecing with enums.
Yes, the schema was written by hand. The new validation helped us highlight an issue/oversight from us in the schema, i.e. we were trying to set the Survey field to NULL if a survey was deleted, however the field cannot be NULL as it's required.
So that's appreciated.

Also, yes - everything you said makes sense. I've discussed with the team and we're happy for you to close this issue and we'll review both our Prisma versions and our database models going forward.

Thanks so much!

[Jolg42]

Great news! Thanks for the speedy reply as well, we're very happy this helped your team 👍🏼

[janpio]

Thanks for the feedback, perfect outcome here then. Bug fixed in patch, our validation did its job, and your issue helped us to confirm all of this.

Enjoy your weekend!

[elie222]

4.6.1 also fixed the enum error for us. 4.6.0 caused the problem.