prisma: MySQL does not support `onDelete: setDefault`

When trying to migrate a super simple schema to MySQL with an onDelete: setDefault, this happens:

C:\Users\Jan\Documents\throwaway\setdefault>npx prisma db push     
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": MySQL database "purple_kingfisher" at "mysql-db-provision.cm0mkpwj8arx.eu-central-1.rds.amazonaws.com:3306"
Error: Cannot add foreign key constraint
   0: sql_migration_connector::sql_database_step_applier::apply_migration
             at migration-engine\connectors\sql-migration-connector\src\sql_database_step_applier.rs:11
   1: migration_core::api::SchemaPush
             at migration-engine\core\src\api.rs:187
model OnDeleteSetDefaultParent {
  id                Int                                @id @default(autoincrement())
  name              String                             @unique
  mandatoryChildren OnDeleteSetDefaultMandatoryChild[]
}

model OnDeleteSetDefaultMandatoryChild {
  id       Int                      @id @default(autoincrement())
  name     String                   @unique
  parent   OnDeleteSetDefaultParent @relation(fields: [parentId], references: [id], onDelete: SetDefault)
  parentId Int                      @default(1)
}

Per our documentation this should fundamentally work (if maybe a bit different than expected): image

But looking at the MySQL docs, this does not seem to be supported at all:

SET DEFAULT: This action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

Similar for MariaDB:

The SET DEFAULT action is not supported.

We even mention similar in our engines comments: https://github.com/prisma/prisma-engines/blob/ccf3dc944acdabb431947150e12b984b34c538cd/query-engine/connector-test-kit-rs/query-engine-tests/tests/new/ref_actions/on_delete/set_default.rs#L1 https://github.com/prisma/prisma-engines/blob/ccf3dc944acdabb431947150e12b984b34c538cd/migration-engine/migration-engine-tests/tests/migrations/relations.rs#L565-L566

We should adapt our validation to not allow this, and update our documentation afterwards as well. No need for our users to waste their time with this.

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Comments: 17 (17 by maintainers)

Most upvoted comments

I have summarised my findings in this internal notion doc.

I have just found out that MySQL 8.0 docs report the following:

SET DEFAULT: This action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

I confirm that ON DELETE SET DEFAULT is supported in CREATE TABLE statements for the following database versions:

  • mysql:8 and beyond
  • mariadb:10.9 and beyond (possibly earlier versions as well, but definitely not mariadb:10.0)

mysql:5.6 thus does not support it.

If we say we only support InnoDB, I’m OK validating against using SetDefault on MySQL.