Naming Conflict with current Foreign Key auto-naming

[ratiofu]

This is a follow-up to #510 and #701. The current strategy for auto-naming foreign key constraints can fail for a particular combination of table names and column names (and does for us). Given these table definitions:

object Root : LongIdTable("root")
object SomeType : LongIdTable("some_type") {
  init { id.references(Root.id) }
}
object Some : LongIdTable("some") {
  val type = reference("type_id", SomeType.id)
}

this test will fail:

@Test
fun `generates different FK constraint names for H2`() {
  val database = Database.connect("jdbc:h2:mem:TEST_FK_DDL", "org.h2.Driver", "root", "")
  transaction(database) {
    val ddl = listOf(Root, SomeType, Some).flatMap { it.ddl }.joinToString(";\n\n")
    exec(ddl) // <-- throws `Constraint "FK_SOME_TYPE_ID_ID" already exists`
  }
}

with:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Constraint "FK_SOME_TYPE_ID_ID" already exists;

The root cause being that both table some_type and some define a foreign key constraint auto-named as "FK_SOME_TYPE_ID_ID"

At a minimum, the target table name should be part of the automatically generated constraint name, although even then a naming conflict may be possible. The preferred solution would be to support custom foreign key names, as per #510. That issue is labeled as "good-first-issue" and I looked into how that could be accomplished, but I could not get the local Gradle build to work on my Mac; it hangs with

Exception in thread "driver_launched_mysqld_1" com.mysql.management.util.WrappedException: java.io.IOException: Cannot run program "[...]test-c.mxj/mysql-c.mxj/bin/mysqld": error=86, Bad CPU type in executable

Do you have a "Contributor Guide" somewhere? This is empty.

[Tapac]

Thank you for a report, in the next release, there will be the possibility to define foreign keys names.

I'll actualize the wiki page soon, but could you tell me which MacOS version do you use as I run tests via Gradle on El Capitan 10.11.6 without any problems.

[ratiofu]

I am on Catalina, 10.15.3

[ratiofu]

Considering that #510 has long been resolved, I will close this.