Exposed: Name for primary key constraint

[Edvinas01]

At the moment there seems to be no way to set a constraint name for primary key:

object TestTable : IdTable<Long>("test") {
    override val id = long("id")
        .primaryKey() // <- cant set the name
        .autoIncrement("test_table_id_seq")
        .entityId()
}

Exposed seems to always generate it automatically using this pattern:

pk_$tableName

I would like to have the ability to use a different naming pattern as I'm following this naming convention.

[AndreasVolkmann]

I also think there should be a way to customize this.

When defining a table name with schema like so:
object Users : Table("dbo.users")

Then the constraint name of the primary key will cause an exception CONSTRAINT pk_dbo.users

At least for Sql Server: SQLServerException: Incorrect syntax near '.'

[Darych]

I think we can add pk name in Table class and initialize it with the first non-null value from primaryKey() function.
@Tapac I'm going to add this enhancement, if you are OK with that.

[Tapac]

@Darych , sure, will be waiting for your PR. Thank you.

[Darych]

Please review a design, which I'm going to use.

primaryKey() function will have an argument name with an empty string as a default value.

Exposed will create named PK if name is not blank, even if the table has only one column in PK:

object : Table("tab") {
            val id1 = integer("id1").primaryKey(name="PK_name")
            val col2 = integer("col2")
        }
}

Generated SQL:

CREATE TABLE tab (id1 INT, col2 INT, CONSTRAINT PK_name PRIMARY KEY (id1))

If a table has multiple columns in PK, only the first name setting will be applied:

1) Name specified in the first primaryKey() function

object : Table("tab") {
            val id1 = integer("id1").primaryKey(name="PK_name")
            val id2 = integer("id2").primaryKey()
        }
}

Generated SQL:

CREATE TABLE tab (id1 INT, id2 INT, CONSTRAINT PK_name PRIMARY KEY (id1, id2))

2) Name specified in the second primaryKey() function

object : Table("tab") {
            val id1 = integer("id1").primaryKey()
            val id2 = integer("id2").primaryKey(name="PK_name")
        }
}

Generated SQL:

CREATE TABLE tab (id1 INT, id2 INT, CONSTRAINT PK_name PRIMARY KEY (id1, id2))

3) Name specified in both primaryKey() functions. This needs to be disucssed. I think the first one wins. But it's possible to throw an exception in this case.

object : Table("tab") {
            val id1 = integer("id1").primaryKey(name="First_PK")
            val id2 = integer("id2").primaryKey(name="Second_PK")
        }
}

Generated SQL:

CREATE TABLE tab (id1 INT, id2 INT, CONSTRAINT First_PK PRIMARY KEY (id1, id2))

[Edvinas01]

Personally I think option regarding the exception is best. Otherwise you will get implicit behavior - library users will have to guess whats going to happen.

However I think there is an even better alternative. Why throw exceptions when you can completely avoid them. I'd suggest to rethink the way primary key is defined altogether (rough idea):

1. Create a class for holding primary key info:

data class PrimaryKey(val name: String, val columns: List<Column<*>>)

1. Extend Table class so that it could hold this data:

open class Table(name: String = ""): ColumnSet(), DdlAware {
    open val primaryKey = PrimaryKey("", emptyList())
}

1. Specify a custom primary key:

object TestTable : Table("tab") {
    val id1 = integer("id1")
    val id2 = integer("id2")

    override val primaryKey = PrimaryKey("pk_something", listOf(id1, id2))
}

Though there a couple of huge drawbacks to this - it will break a lot of existing code. It also wont be as easy to implement.

[Tapac]

@Darych , you forgot about an index field of current primaryKey() function, it was added to not force users to keep specific order of columns.

@Edvinas01 , I like your approach, it's possible to make a primaryKey field an optional (with default null) and init its value from existing Column<*>primaryKey() functions if it wasn't overridden by a user. Otherwise, throw an exception.

[Darych]

@Tapac I just did not mention it :) Both parameters should have default values and assigned using named parameters.

@Edvinas01 Thank you! Agree with you.

Possibly we can mark Column<*>.primaryKey() as deprecated if we don't want to support both methods.
But currently, I agree with @Tapac, we need to add columns from Column<*>.primaryKey() to new Table.primaryKey field.

[Tapac]

@Darych , @Edvinas01 , any of you want to provide PR? Or should I fix it?

[Darych]

I'm going to provide a PR for this.
@Tapac are you OK with deprecating Column<*>.primaryKey() ?

[Tapac]

@Darych , I would advise to make it as a following:

1. Add support for new override val primaryKey declaration but it should optional (as having a primary key on a table is not mandatory) and initialize/resolve it from existing primary keys. Also, all predefined PK tables (IntIdTable/LongIdTable/etc) should be updated with new implementation. It will cover a large percent of PK usages.
2. Make a release and describe that in release notes/wiki.
3. On a next large release deprecate Column<*>.primaryKey() with warn to use new approach.
4. On a next large release deprecate with error.
5. Remove.

[Darych]

@Tapac OK, agree with this approach.
I will do it in next few weeks.

[SchweinchenFuntik]

object Persons : IntIdTable() {
    val name = varchar("name", 100)
    val type = enumeration("type", TypePerson::class)
}

object Lands : Table() {
    val landNumber = integer("landNumber").primaryKey()
    val identifier = varchar("identifier", 1).primaryKey()
    val square = double("square")
    val currentOwner = reference("currentOwner", Persons)
}

object LandOwnershipHistory : IntIdTable() {
    val ownership = reference("ownership", Persons)
    val land = reference("land", Lands.landNumber) // use PK identifier
}

In this scheme, it is impossible to make a reference from two columns.

[Tapac]

@SchweinchenFuntik , it's impossible to reference on two columns from a single column. Also, column might not be PK to be referenced on.
Could you clarify the problem?

[SchweinchenFuntik]

@Tapac Sorry for delay. Here I have such a scheme. How to implement LandOwnershipHistory.land correctly

object Persons : IntIdTable() {
    val name = varchar("name", 100)
    val type = enumeration("type", TypePerson::class)
}

object Lands : Table() {
    val landNumber = integer("landNumber").primaryKey()
    val identifier = varchar("identifier", 1).primaryKey()
    val square = double("square")
    val currentOwner = reference("currentOwner", Persons)
}

object LandOwnershipHistory : IntIdTable() {
    val ownership = reference("ownership", Persons)
    val land = reference("land", Lands.landNumber) // use PK identifier
}

[Tapac]

@SchweinchenFuntik , your mapping looks valid, what do you mean by correctly?

[SchweinchenFuntik]

@Tapac I have a primary key, which consists of two columns. How do I use this key? The scheme above is correct from the point of view when my key consists of two columns ?

[Tapac]

@SchweinchenFuntik, create two reference columns one for each from primary key.

[Darych]

Guys, I'm really sorry. I don't have enough time to do this. Please feel free to fix the issue.
If I will have time and issue won't be in progress, I'll take it.

[Tapac]

Fixed in master will be available in 0.20.1