MySQL error trying to delete a constraint

[bottlehall]

I've got a Migration that adds a foreign key field to a Model:

struct UpdateUnitTable1: Migration {
    func prepare(on database: Database) -> EventLoopFuture<Void> {
        database.schema("Unit")
            .field("createdByUserId", .uuid)
            .foreignKey("createdByUserId", references: "User", "id", name: "created_user_id")
            .update()
    }

    func revert(on database: Database) -> EventLoopFuture<Void> {
        database.schema("Unit")
            .deleteConstraint(name: "created_user_id")
            .deleteField("createdByUserId")
            .update()
    }
}

The prepare works, but revert errors with:

caught error: "previousError(MySQL error: Server error: Cannot drop column 'createdByUserId': needed in a foreign key constraint '7b75a8aae1173c6c1f04cb626cadff7a5c321cf0')"

Thinking it might be that it can't be done in a single update to the schema, I tried:

    func revert(on database: Database) -> EventLoopFuture<Void> {
        database.schema("Unit")
            .deleteConstraint(name: "created_user_id")
            .update()
            .flatMap { _ in
            return database.schema("Unit")
                .deleteField("createdByUserId")
                .update()
            }
    }

However, this just gives a different error:

caught error: "previousError(MySQL error: Server error: Cannot drop index '7b75a8aae1173c6c1f04cb626cadff7a5c321cf0': needed in a foreign key constraint)"

Reverting to the original, single update and reversing the order of deleting the field and constraint doesn't help either.

Looking through the fluent source code, it looks like it doesn't take account of the existence of the index that is created with the constraint so doesn't try to. delete it.

[0xTim]

It looks like on MySQL it's creating an index as well as the constraint which needs to be deleted in the revert as well. You'll probably need to drop down to raw SQL for this

[bottlehall]

Thank you, @0xTim. I have done some more investigation.

If I use MySQL client on the table created by the prepare above and try to delete the index manually, then I get the same error about it being needed in the foreign key constraint. However, if I use:

ALTER TABLE Unit DROP FOREIGN KEY 7b75a8aae1173c6c1f04cb626cadff7a5c321cf0

Then it works and deletes the index as well. So, the raw query will have to delete the foreign key rather than the index, which suggests that there is a problem in the deleteConstraint approach. Looking at the MySQL reference, it says you can't delete a foreign key using the generic constraint syntax, you need to use the specific syntax of deleting the foreign key as above. However, I've just tried:

ALTER TABLE Unit DROP CONSTRAINT 7b75a8aae1173c6c1f04cb626cadff7a5c321cf0

And this deletes the foreign key but leaves the index of the same name untouched.

Postgres does let you delete a foreign key via the constraint syntax.

I think there is a (second) issue that although I have attempted to name the constraint in the prepare, this isn't showing up in MySQL - the foreign key/index name is auto-generated as above. If we can fix the missing name then it is fairly trivial to do the raw SQL to delete the foreign key manually using the statement above, but if not then it will take an initial raw SQL query to identify auto-generated name and then use this in the raw query to delete the foreign key.

[kevinzhow]

Same issue here

[kevinzhow]

After some research, I sloved this problem by writing raw sql

import Foundation
import Fluent
import Vapor
import SQLKit

struct CreateArticleHeadCommit: AsyncMigration {
    func prepare(on database: Database) async throws {
        try await database.schema(ArticleEntry.schema)
            .field("head_commit_id", .uuid)
            .update()


        let sqlDB = database as! SQLDatabase

        let _ = try await sqlDB.raw(
"""
ALTER TABLE \(ArticleEntry.schema)
ADD CONSTRAINT FK_head_commit_id
FOREIGN KEY (head_commit_id)
REFERENCES \(ArticleCommitEntry.schema)(id);
"""
        ).all()

    }

    func revert(on database: Database) async throws {
        let sqlDB = database as! SQLDatabase

        let _ = try await sqlDB.raw(
"""
ALTER TABLE \(ArticleEntry.schema)
DROP CONSTRAINT FK_head_commit_id;
"""
        ).all()

        try await database.schema(ArticleEntry.schema)
            .deleteField("head_commit_id").update()
    }
}

[kevinzhow]

Here is my new approach @0xTim

struct CreateArticleHeadCommit: AsyncMigration {
    func prepare(on database: Database) async throws {
        try await database.schema(ArticleEntry.schema)
            .field("head_commit_id", .uuid)
            .foreignKey("head_commit_id",
                        references: ArticleCommitEntry.schema,
                        "id",
                        onDelete: .setNull,
                        name: "created_head_commit_id")
            .update()

    }

    func revert(on database: Database) async throws {
        try await database.schema(ArticleEntry.schema)
                    .deleteConstraint(name: "created_head_commit_id")
                    .update()

        try await database.schema(ArticleEntry.schema).deleteField("head_commit_id")
            .update()
    }
}

This approach result in the same as this issue

MySQL error: Server error: Cannot drop index '2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14': needed in a foreign key constraint
Swift/ErrorType.swift:200: Fatal error: Error raised at top level: MySQL error: Server error: Cannot drop index '2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14': needed in a foreign key constraint

to debug this problem, I traced the mysql log

migration

12 Connect	[email protected] on vapor_database using TCP/IP
		    12 Prepare	CREATE TABLE IF NOT EXISTS `_fluent_migrations`(`id` VARBINARY(16) PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `batch` BIGINT NOT NULL, `created_at` DATETIME(6), `updated_at` DATETIME(6), CONSTRAINT `eb3ee69e0c062ede0b815d412472c764ccb82e41` UNIQUE (`name`))
		    12 Execute	CREATE TABLE IF NOT EXISTS `_fluent_migrations`(`id` VARBINARY(16) PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `batch` BIGINT NOT NULL, `created_at` DATETIME(6), `updated_at` DATETIME(6), CONSTRAINT `eb3ee69e0c062ede0b815d412472c764ccb82e41` UNIQUE (`name`))
		    12 Close stmt	
		    12 Prepare	SELECT COUNT(`_fluent_migrations`.`id`) AS `aggregate` FROM `_fluent_migrations` WHERE `_fluent_migrations`.`name` IN (? , ? , ?)
		    12 Execute	SELECT COUNT(`_fluent_migrations`.`id`) AS `aggregate` FROM `_fluent_migrations` WHERE `_fluent_migrations`.`name` IN ('CreateArticleCommitEntry' , 'CreateArticleHeadCommit' , 'CreateArticleEntry')
		    12 Close stmt	
		    13 Connect	[email protected] on vapor_database using TCP/IP
		    13 Prepare	SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations`
		    13 Execute	SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations`
		    13 Close stmt	
220310  6:00:47	    14 Connect	[email protected] on vapor_database using TCP/IP
		    14 Prepare	SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` ORDER BY `_fluent_migrations`.`batch` DESC LIMIT 1
		    14 Execute	SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` ORDER BY `_fluent_migrations`.`batch` DESC LIMIT 1
		    14 Close stmt	
		    14 Prepare	SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations`
		    14 Execute	SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations`
		    14 Close stmt	
		    14 Prepare	CREATE TABLE `article_entry`(`id` VARBINARY(16) PRIMARY KEY, `created_at` DATETIME(6), `updated_at` DATETIME(6), `deleted_at` DATETIME(6))
		    14 Execute	CREATE TABLE `article_entry`(`id` VARBINARY(16) PRIMARY KEY, `created_at` DATETIME(6), `updated_at` DATETIME(6), `deleted_at` DATETIME(6))
		    14 Close stmt	
		    14 Prepare	INSERT INTO `_fluent_migrations` (`name`, `id`, `created_at`, `batch`, `updated_at`) VALUES (?, ?, ?, ?, ?)
		    14 Execute	INSERT INTO `_fluent_migrations` (`name`, `id`, `created_at`, `batch`, `updated_at`) VALUES ('App.CreateArticleEntry', '{7
    \?L\?\?\?$x', TIMESTAMP'2022-03-10 06:00:47.954614', 1, TIMESTAMP'2022-03-10 06:00:47.954614')
		    14 Close stmt	
		    14 Prepare	CREATE TABLE IF NOT EXISTS `_fluent_enums`(`id` VARBINARY(16) PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `case` VARCHAR(255) NOT NULL, CONSTRAINT `88ee0ca2ba14b91c020671c68f4dd39aa4ed942d` UNIQUE (`name`, `case`))
		    14 Execute	CREATE TABLE IF NOT EXISTS `_fluent_enums`(`id` VARBINARY(16) PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `case` VARCHAR(255) NOT NULL, CONSTRAINT `88ee0ca2ba14b91c020671c68f4dd39aa4ed942d` UNIQUE (`name`, `case`))
		    14 Close stmt	
		    14 Prepare	INSERT INTO `_fluent_enums` (`id`, `name`, `case`) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)
		    14 Execute	INSERT INTO `_fluent_enums` (`id`, `name`, `case`) VALUES ('\?M\?\?-!E\?\ZŶ\?7\?', 'article_type', 'novel'), ('\?\?\?S\?Hx\?\"5;	\?\?\?', 'article_type', 'news'), (':N\?o\?K\?6_\?SXT', 'article_type', 'manga')
		    14 Close stmt	
		    14 Prepare	DELETE FROM `_fluent_enums` WHERE `_fluent_enums`.`name` = ? AND 1 = 0
		    14 Execute	DELETE FROM `_fluent_enums` WHERE `_fluent_enums`.`name` = 'article_type' AND 1 = 0
		    14 Close stmt	
		    14 Prepare	SELECT `_fluent_enums`.`id` AS `_fluent_enums_id`, `_fluent_enums`.`name` AS `_fluent_enums_name`, `_fluent_enums`.`case` AS `_fluent_enums_case` FROM `_fluent_enums` WHERE `_fluent_enums`.`name` = ?
		    14 Execute	SELECT `_fluent_enums`.`id` AS `_fluent_enums_id`, `_fluent_enums`.`name` AS `_fluent_enums_name`, `_fluent_enums`.`case` AS `_fluent_enums_case` FROM `_fluent_enums` WHERE `_fluent_enums`.`name` = 'article_type'
		    14 Close stmt	
		    14 Prepare	CREATE TABLE IF NOT EXISTS `_fluent_enums`(`id` VARBINARY(16) PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `case` VARCHAR(255) NOT NULL, CONSTRAINT `88ee0ca2ba14b91c020671c68f4dd39aa4ed942d` UNIQUE (`name`, `case`))
		    14 Execute	CREATE TABLE IF NOT EXISTS `_fluent_enums`(`id` VARBINARY(16) PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `case` VARCHAR(255) NOT NULL, CONSTRAINT `88ee0ca2ba14b91c020671c68f4dd39aa4ed942d` UNIQUE (`name`, `case`))
		    14 Close stmt	
		    14 Prepare	DELETE FROM `_fluent_enums` WHERE `_fluent_enums`.`name` = ? AND 1 = 0
		    14 Execute	DELETE FROM `_fluent_enums` WHERE `_fluent_enums`.`name` = 'article_type' AND 1 = 0
		    14 Close stmt	
		    14 Prepare	SELECT `_fluent_enums`.`id` AS `_fluent_enums_id`, `_fluent_enums`.`name` AS `_fluent_enums_name`, `_fluent_enums`.`case` AS `_fluent_enums_case` FROM `_fluent_enums` WHERE `_fluent_enums`.`name` = ?
		    14 Execute	SELECT `_fluent_enums`.`id` AS `_fluent_enums_id`, `_fluent_enums`.`name` AS `_fluent_enums_name`, `_fluent_enums`.`case` AS `_fluent_enums_case` FROM `_fluent_enums` WHERE `_fluent_enums`.`name` = 'article_type'
		    14 Close stmt	
		    14 Prepare	CREATE TABLE `article_commit_entry`(`id` VARBINARY(16) PRIMARY KEY, `article_id` VARBINARY(16) NOT NULL, `author` JSON NOT NULL, `title` JSON NOT NULL, `article_type` ENUM('manga', 'news', 'novel') NOT NULL, `content` VARCHAR(255) NOT NULL, `created_at` DATETIME(6), `updated_at` DATETIME(6), `deleted_at` DATETIME(6), CONSTRAINT `0c5b0271fa7fa8d07357c01c906eeb90abce93b5` FOREIGN KEY (`article_id`) REFERENCES `article_entry` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
		    14 Execute	CREATE TABLE `article_commit_entry`(`id` VARBINARY(16) PRIMARY KEY, `article_id` VARBINARY(16) NOT NULL, `author` JSON NOT NULL, `title` JSON NOT NULL, `article_type` ENUM('manga', 'news', 'novel') NOT NULL, `content` VARCHAR(255) NOT NULL, `created_at` DATETIME(6), `updated_at` DATETIME(6), `deleted_at` DATETIME(6), CONSTRAINT `0c5b0271fa7fa8d07357c01c906eeb90abce93b5` FOREIGN KEY (`article_id`) REFERENCES `article_entry` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
		    14 Close stmt	
		    14 Prepare	INSERT INTO `_fluent_migrations` (`batch`, `updated_at`, `id`, `name`, `created_at`) VALUES (?, ?, ?, ?, ?)
		    14 Execute	INSERT INTO `_fluent_migrations` (`batch`, `updated_at`, `id`, `name`, `created_at`) VALUES (1, TIMESTAMP'2022-03-10 06:00:47.988511', 'җ\?\?\?\?Hn\?Ε.\?\?\?\?', 'App.CreateArticleCommitEntry', TIMESTAMP'2022-03-10 06:00:47.988511')
		    14 Close stmt	
		    14 Prepare	ALTER TABLE `article_entry` ADD `head_commit_id` VARBINARY(16) , ADD CONSTRAINT `2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14` FOREIGN KEY (`head_commit_id`) REFERENCES `article_commit_entry` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION
		    14 Execute	ALTER TABLE `article_entry` ADD `head_commit_id` VARBINARY(16) , ADD CONSTRAINT `2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14` FOREIGN KEY (`head_commit_id`) REFERENCES `article_commit_entry` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION
220310  6:00:48	    14 Close stmt	
		    14 Prepare	INSERT INTO `_fluent_migrations` (`batch`, `updated_at`, `id`, `name`, `created_at`) VALUES (?, ?, ?, ?, ?)
		    14 Execute	INSERT INTO `_fluent_migrations` (`batch`, `updated_at`, `id`, `name`, `created_at`) VALUES (1, TIMESTAMP'2022-03-10 06:00:48.003450', '\?/eXk\?G\?\?<\?\?o\?i', 'App.CreateArticleHeadCommit', TIMESTAMP'2022-03-10 06:00:48.003450')
		    14 Close stmt	
		    13 Quit	
		    14 Quit	
		    12 Quit	

revert

220310  6:03:30	    15 Connect	[email protected] on vapor_database using TCP/IP
		    15 Prepare	CREATE TABLE IF NOT EXISTS `_fluent_migrations`(`id` VARBINARY(16) PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `batch` BIGINT NOT NULL, `created_at` DATETIME(6), `updated_at` DATETIME(6), CONSTRAINT `eb3ee69e0c062ede0b815d412472c764ccb82e41` UNIQUE (`name`))
		    15 Execute	CREATE TABLE IF NOT EXISTS `_fluent_migrations`(`id` VARBINARY(16) PRIMARY KEY, `name` VARCHAR(255) NOT NULL, `batch` BIGINT NOT NULL, `created_at` DATETIME(6), `updated_at` DATETIME(6), CONSTRAINT `eb3ee69e0c062ede0b815d412472c764ccb82e41` UNIQUE (`name`))
		    15 Close stmt	
		    15 Prepare	SELECT COUNT(`_fluent_migrations`.`id`) AS `aggregate` FROM `_fluent_migrations` WHERE `_fluent_migrations`.`name` IN (? , ? , ?)
		    15 Execute	SELECT COUNT(`_fluent_migrations`.`id`) AS `aggregate` FROM `_fluent_migrations` WHERE `_fluent_migrations`.`name` IN ('CreateArticleCommitEntry' , 'CreateArticleHeadCommit' , 'CreateArticleEntry')
		    15 Close stmt	
		    16 Connect	[email protected] on vapor_database using TCP/IP
		    16 Prepare	SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` ORDER BY `_fluent_migrations`.`batch` DESC LIMIT 1
		    16 Execute	SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` ORDER BY `_fluent_migrations`.`batch` DESC LIMIT 1
		    16 Close stmt	
		    16 Prepare	SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` WHERE `_fluent_migrations`.`batch` = ?
		    16 Execute	SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` WHERE `_fluent_migrations`.`batch` = 1
		    16 Close stmt	
		    17 Connect	[email protected] on vapor_database using TCP/IP
		    17 Prepare	SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` ORDER BY `_fluent_migrations`.`batch` DESC LIMIT 1
		    17 Execute	SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` ORDER BY `_fluent_migrations`.`batch` DESC LIMIT 1
		    17 Close stmt	
		    17 Prepare	SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` WHERE `_fluent_migrations`.`batch` = ?
		    17 Execute	SELECT `_fluent_migrations`.`id` AS `_fluent_migrations_id`, `_fluent_migrations`.`name` AS `_fluent_migrations_name`, `_fluent_migrations`.`batch` AS `_fluent_migrations_batch`, `_fluent_migrations`.`created_at` AS `_fluent_migrations_created_at`, `_fluent_migrations`.`updated_at` AS `_fluent_migrations_updated_at` FROM `_fluent_migrations` WHERE `_fluent_migrations`.`batch` = 1
		    17 Close stmt	
		    17 Prepare	ALTER TABLE `article_entry` DROP KEY `2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14`
		    17 Execute	ALTER TABLE `article_entry` DROP KEY `2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14`
		    17 Close stmt	
		    17 Quit	
		    15 Quit	
		    16 Quit	

[kevinzhow]

@0xTim just wrote a new unit test to debug deleteConstraint

final class DatabaseSQLTests: XCTestCase {
    func testDatabaseSQLTests() throws {
        let db = DummyDatabaseForTestSQLSerializer()
        try db.schema(ArticleEntry.schema)
                    .deleteConstraint(name: "created_head_commit_id")
                    .update().wait()

        print(db.sqlSerializers)
    }
}

the sql looks normal

[SQLKit.SQLSerializer(sql: "ALTER TABLE \"article_entry\" DROP CONSTRAINT \"created_head_commit_id\"", binds: [], database: AppTests.DummyDatabaseForTestSQLSerializer)]

so why mysql recieved these sql?

Prepare	ALTER TABLE `article_entry` DROP KEY `2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14`
		    17 Execute	ALTER TABLE `article_entry` DROP KEY `2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14`

I'll keep digging

[kevinzhow]

According to MySQL's reference Conditions and Restrictions

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. 
In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. 
Such an index is created on the referencing table automatically if it does not exist. 
This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

So yes, adding foregin key reference will also produce an index.

But when we want to delete Foregin Key Reference field, there is no need to delete index first

Seems Fluent tries to delete the index first.

[kevinzhow]

I made a breakpoint in FluentMySQLDatabase#48

Looks like it convert deleteConstraint into

ALTER TABLE `article_entry` DROP KEY `2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14`

[kevinzhow]

Okey, I find out whats the problem

https://github.com/vapor/fluent-kit/blob/9d47c328bf83999968c12a3bc94ead1d706ad4a9/Sources/FluentSQL/SQLSchemaConverter.swift#L280

    public func serialize(to serializer: inout SQLSerializer) {
        if serializer.dialect.name == "mysql" {
            serializer.write("KEY ")
        } else {
            serializer.write("CONSTRAINT ")
        }
        let normalizedName = serializer.dialect.normalizeSQLConstraint(identifier: name)
        normalizedName.serialize(to: &serializer)
    }

Here we can find out that at runtime, SQLDropConstraint was converted to KEY instead of CONSTRAINT

[kevinzhow]

@0xTim just wrote a new unit test to debug deleteConstraint

final class DatabaseSQLTests: XCTestCase {
    func testDatabaseSQLTests() throws {
        let db = DummyDatabaseForTestSQLSerializer()
        try db.schema(ArticleEntry.schema)
                    .deleteConstraint(name: "created_head_commit_id")
                    .update().wait()

        print(db.sqlSerializers)
    }
}

the sql looks normal

[SQLKit.SQLSerializer(sql: "ALTER TABLE \"article_entry\" DROP CONSTRAINT \"created_head_commit_id\"", binds: [], database: AppTests.DummyDatabaseForTestSQLSerializer)]

so why mysql recieved these sql?

Prepare	ALTER TABLE `article_entry` DROP KEY `2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14`
		    17 Execute	ALTER TABLE `article_entry` DROP KEY `2e2dd11dd7a4d4a0f5f979267ceaa4c9575eae14`

I'll keep digging

So here is the truth, when db is mysql, it produces KEY which failed the deleteConstraint

So why we are using KEY instead of CONSTRAINT?

@bottlehall

[0xTim]

@gwynne any idea?

[kevinzhow]

I leave some new invesgation on PR https://github.com/vapor/fluent-kit/pull/492#issuecomment-1063959265

@gwynne I would like to know your oppion.

[gwynne]

This should have been fixed by vapor/fluent-kit#522

[gwynne]

This should now be actually fixed, see the release notes for details on how to update your code.

[bottlehall]

Hi

Sadly, I can’t reproduce the fix with my minimal example previous submitted. I’ve tried using:

            .field("imagesUploadedByUserId", .uuid, .references("User", "id"))
            .foreignKey("idkId", references: "User", "id", name: "waddyaKnow")

To create the foreign key fields. And:

            .deleteForeignKey(name:"waddyaKnow”)
	.deleteConstraint(name:”waddyaKnow”)

Attempting to delete the field.

In all cases, it reports that it is failing to delete the column/key using the normalised constraint/key name, such as:

0c2da0c13abec57e8c4ccd235ae7b4d070b2e686

I’ve also uncovered a similar issue with multiple-index ‘unique’ indices, which I have logged.

N

On 30 Jul 2023, at 03:11, Gwynne Raskind @.***> wrote:

Closed #722 https://github.com/vapor/fluent/issues/722 as completed.

— Reply to this email directly, view it on GitHub https://github.com/vapor/fluent/issues/722#event-9955893869, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABK5DRI5JAR3YND4EE5XBPDXSW7EHANCNFSM42TNSK4A. You are receiving this because you were mentioned.