MySQL delete multiple-field index fails

[bottlehall]

Creating a mutliple-field index works, but attempting to delete it results in the error:

[Migrator] Failed revert: MySQL error: Server error: Cannot drop index 'ef4c7643e296981f64d9496e40d9f80240256ffa': needed in a foreign key constraint

The following Migration reproduces the problem:

    struct UpdateRollTable3: AsyncMigration {
        func prepare(on database: Database) async throws {
            try await database.schema("Roll")
                .unique(on: "eventId", "userId")
                .update()
        }

        func revert(on database: Database) async throws {
            try await database.schema("Roll")
                .deleteUnique(on: "eventId", "userId")
                .update()
        }
    }

I expected the delete to work.

Vapor 18.7.1 MacOS 13.5 Swift 5.8

[gwynne]

This is not a Fluent issue; it's a limitation of how MySQL implements foreign keys. Foreign keys are always backed by an index on the referencing table's included columns, which is automatically created if no suitable index already exists - but if a suitable index does exist, such as a unique key, it will be automatically adopted. To drop an index that this has happened to, the foreign key must be dropped first. If the foreign key is still needed, it can be re-added in the same alter statement, e.g.:

try await database.schema("Roll")
    // To delete a foreign key, it mus be specified as a constraint using its original parameters:
    .deleteConstraint(.constraint(.foreignKey(
        [.key("userId")],
        User.schema, [.key(.id)],
        onDelete: .noAction, onUpdate: .noAction
    )))
    // OR, it must be specified by name using this syntax:
    .deleteForeignKey(name: "nameUsedWhenCreatingIt")

    // Then drop the index that it depended on:
    .deleteUnique(on: "eventId", "userId")

    // Then it can be recreated:
    .foreignKey("userId", references: User.schema, .id, onDelete: .noAction, onUpdate: .noAction)

    .update()