How to fix "java.sql.SQLException: Cannot add foreign key constraint." when creating table with double primary/foreign key relationship

Question

I'm setting up a database for a project I am working on using Kotlin and Jetbrain's Exposed SQL library. and I am trying to establish a foreign key relationship between two tables, cw_cache (parent table) and cw_requests (child table).

This setup works fine with an Sqlite database, but does not work when I try to create the tables in a MySQL server database. I get the error "java.sql.SQLException: Cannot add foreign key constraint."

I've already looked at similar problems on here and made sure that the columns in the parent and child tables have the same data type, that the columns in the parent table are actually keys and that the cw_cache table is being created before the cw_requests table.

When I run SHOW ENGINE INNODB STATUS; to see the foreign key error, I see this:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2019-04-05 18:29:17 2e94 Error in foreign key constraint of table coursewatcher/cw_requests:
FOREIGN KEY (term) REFERENCES cw_cache(term) ON DELETE RESTRICT ON UPDATE RESTRICT):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

Diagram from the Sqlite database: https://gyazo.com/220dd4b1a3d301419e0b8b73bfc80a68

Relevant Code:

cw_cache table:

object Cache : Table("cw_cache") {
    val crn = varchar("crn", 5).primaryKey()
    val term = varchar("term", 6).primaryKey()

    // other column initializers
}

cw_request table:

object Requests : Table("cw_requests") {
    val id = long("id").primaryKey()
    val orderId = long("order_id") references Orders.id
    val crn = varchar("crn", 5) references Cache.crn
    val term = varchar("term", 6) references Cache.term

    // other column initializers
}

Answer 1

Declaring composite foreign keys is not currently implemented with the Exposed framework, according to this issue: https://github.com/JetBrains/Exposed/issues/511

A reply on that issue gives an example of code for a manual workaround:

val t = TransactionManager.current()
val fk = ForeignKeyConstraint("fk_name",
                    t.identity(TableA), "{t.identity(TableA.idA)}, {t.identity(TableA.idA)}",
                    t.identity(TableB), "{t.identity(TableB.idA)}, {t.identity(TableB.idA)}",
                    ReferenceOption.RESTRICT,
                    ReferenceOption.RESTRICT)
t.exec(fk.createStatement().firsts())

I have not used Kotlin or the Exposed framework, so don't ask me how to employ that code example. If it makes sense to you, good luck with it.