Composite foreign key

[SackCastellon]

I have this two tables:

object TableA : Table("TableA") {
    val idA = integer("id_a").primaryKey()
    val idB = integer("id_b").primaryKey()
    val value = varchar("value", 42)
}

object TableB : Table("TableB") {
    val idA = integer("id_a").primaryKey() references TableA.idA
    val idB = integer("id_b").primaryKey() references TableA.idB
    val idC = integer("id_c").primaryKey()
    val value = varchar("value", 42)
}

The generated SQL statements look like this:

CREATE TABLE IF NOT EXISTS TableA
(
  id_a    INT,
  id_b    INT,
  "value" VARCHAR(42) NOT NULL,
  CONSTRAINT pk_TableA PRIMARY KEY (id_a, id_b)
);

CREATE TABLE IF NOT EXISTS TableB
(
  id_a    INT,
  id_b    INT,
  id_c    INT,
  "value" VARCHAR(42) NOT NULL,
  CONSTRAINT pk_TableB PRIMARY KEY (id_a, id_b, id_c),
  FOREIGN KEY (id_a) REFERENCES TableA (id_a) ON DELETE RESTRICT ON UPDATE RESTRICT,
  FOREIGN KEY (id_b) REFERENCES TableA (id_b) ON DELETE RESTRICT ON UPDATE RESTRICT
);

This works in some databases like SQLite ot H2, however it doesn't in MySQL or PostgreSQL.

For it to work on all databases whe generated SQL should look like this:

CREATE TABLE IF NOT EXISTS TableA
(
  id_a    INT,
  id_b    INT,
  "value" VARCHAR(42) NOT NULL,
  CONSTRAINT pk_TableA PRIMARY KEY (id_a, id_b)
);

CREATE TABLE IF NOT EXISTS TableB
(
  id_a    INT,
  id_b    INT,
  id_c    INT,
  "value" VARCHAR(42) NOT NULL,
  CONSTRAINT pk_TableB PRIMARY KEY (id_a, id_b, id_c),
  FOREIGN KEY (id_a, id_b) REFERENCES TableA (id_a, id_b) ON DELETE RESTRICT ON UPDATE RESTRICT
);

Is it possible to achive this using the SQL DSL?

[Tapac]

@SackCastellon , it the moment it's possible only the manual way, like :

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())

[AndreyZS]

@SackCastellon , it the moment it's possible only the manual way, like :

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())

@Tapac
Sorry, but a more detailed example is possible. Otherwise, unfortunately ? I can't use

In particular, I don’t understand why the number of arguments is different.

[SettingDust]

What's the workaround for this now?

[naftalmm]

A possible workaround is to declare "virtual" columns, composing referencing and referenced columns respectively, and pass them into the ForeignKeyConstraint constructor.

For convenience, it could be wrapped into auxiliary function:

fun Transaction.foreignKeyCompositeConstraint(
    columnsReferences: Map<Column<*>, Column<*>>,
    onUpdate: ReferenceOption?,
    onDelete: ReferenceOption?,
    name: String? = null,
): ForeignKeyConstraint {
    val fromColumns = columnsReferences.keys
    val fromTable = fromColumns.first().table
    require(fromColumns.all { it.table == fromTable }) { "All referencing columns must belong to the same table" }
    val targetColumns = columnsReferences.values
    val targetTable = targetColumns.first().table
    require(targetColumns.all { it.table == targetTable }) { "All referenced columns must belong to the same table" }

    // this API is private, so redeclare it as an extension method
    fun IdentifierManagerApi.quote(identity: String) = "$quoteString$identity$quoteString".trim()

    val virtualColumnOf = { columns: Iterable<Column<*>> ->
        val someColumn = columns.first()
        val columnsIdentities = columns.joinToString(",") { db.identifierManager.quote(identity(it)) }
        Column<Any>(someColumn.table, columnsIdentities, someColumn.columnType)
    }

    fun Iterable<Column<*>>.joinNamesToString() = joinToString("_") { it.name }

    return ForeignKeyConstraint(
        target = virtualColumnOf(targetColumns),
        from = virtualColumnOf(fromColumns),
        onUpdate = onUpdate,
        onDelete = onDelete,
        name = name ?: "fk_${fromTable.tableName.substringAfter(".")}_${fromColumns.joinNamesToString()}__${targetColumns.joinNamesToString()}"
    )
}

Usage (still manual):

val t = TransactionManager.current()
val fk = t.foreignKeyCompositeConstraint(
    mapOf(TableB.idA to TableA.idA, TableB.idB to TableA.idB),
    ReferenceOption.RESTRICT,
    ReferenceOption.RESTRICT
)
t.exec(fk.createStatement().first())

Works for MySQL, PostgreSQL, H2; will not work for SQLite, due to limitations of its ALTER TABLE command (however it works fine without this workaround)

[minisaw]

hi @naftalmm ,

prior to version 0.37.1 of kotlin-exposed, there was this function in UpdateBuilder.kt:
open operator fun <S> set(column: CompositeColumn<S>, value: S) { ...

in 0.37.1 its signature was changed to:
open operator fun <S : Any> set(column: CompositeColumn<S>, value: S) { ...

is there a compelling reason for S to be a subtype of "Any", instead of "Any?" ?
with this change, i am no longer able to update the value of a nullable composite column with the help of this function, which was possible with version 0.36.1 and earlier.

[naftalmm]

Hi, @minisaw!
This change was added in bd0bdab in the scope of #1275, which has nothing to do with composite foreign key.
Anyway, it looks like a typo and could be fixed.