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):
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.
- https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html#:~:text=SET DEFAULT%3A This action is recognized by the MySQL parser%2C but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.
- https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html#:~:text=SET DEFAULT%3A This action is recognized by the MySQL parser%2C 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)
I have summarised my findings in this internal notion doc.
I have just found out that
MySQL 8.0
docs report the following:I confirm that
ON DELETE SET DEFAULT
is supported inCREATE TABLE
statements for the following database versions:mysql:8
and beyondmariadb:10.9
and beyond (possibly earlier versions as well, but definitely notmariadb: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.