Unable to create index on column that also has a foreign key with createMissingTablesAndColumns

[timfennis]

I have a data structure like this

object Headers : Table("headers") {
    val id = uuid("id").primaryKey()
    // tons more columns
}

object Transactions : Table("transactions") {
    val headerId = (uuid("header_id").references(Headers.id)).index("custom_header_index")
    // tons more columns
}

And I'm trying to create an index on the column with the foreign key. (To quickly query a relation)

But the createMissingTablesAndColumns function seems unwilling to do so probably because of the code bellow

    for (table in tables) {
        val existingTableIndices = currentDialect.existingIndices(table)[table].orEmpty().filterFKeys()
        val mappedIndices = table.indices.filterFKeys()

        existingTableIndices.forEach { index ->
            mappedIndices.firstOrNull { it.onlyNameDiffer(index) }?.let {
                exposedLogger.trace("Index on table '${table.tableName}' differs only in name: in db ${index.indexName} -> in mapping ${it.indexName}")
                nameDiffers.add(index)
                nameDiffers.add(it)
            }
        }

        notMappedIndices.getOrPut(table.nameInDatabaseCase(), {hashSetOf()}).addAll(existingTableIndices.subtract(mappedIndices))

        missingIndices.addAll(mappedIndices.subtract(existingTableIndices))
    }

val mappedIndices = table.indices.filterFKeys() seems to filter out these indexes because it falsely assumes that they are foreign keys. Am I missing something or have I exposed an oversight?

[Tapac]

Did you try to switch an order in column definition from:
(uuid("header_id").references(Headers.id)).index("custom_header_index")
to
uuid("header_id").index("custom_header_index") references Headers.id
Here you declare what you want to create UUID column with name "header_id" and add an index "custom_header_index" to it.
I've got that output on H2:

CREATE TABLE IF NOT EXISTS HEADERS (ID UUID PRIMARY KEY);
CREATE TABLE IF NOT EXISTS TRANSACTIONS (HEADER_ID UUID NOT NULL,  FOREIGN KEY (HEADER_ID) REFERENCES HEADERS(ID) ON DELETE RESTRICT ON UPDATE RESTRICT);
CREATE INDEX custom_header_index ON TRANSACTIONS (HEADER_ID);

[timfennis]

I just tested again with your example. On an empty database it works as expected. But if I create a database without indexes first and then add the indexes and run createMissingTablesAndColumns it doesn't work.

[Tapac]

As I can see the bug was introduced in a 2015 when only MySQL and H2 were supported. And the problem was in the fact what MySQL automatically create an index for any foreign key.