Question 199549 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL unique constraint error when setting up foreign key relation in Exposed (Kotlin)

"I am trying to setup a one-to-one relation between two tables (Users and RefreshTokens) using JetBrains’ Exposed ORM library in Kotlin.

I have defined the tables as follows:

object Users : IdTable<UUID>() {
    override val id = uuid("user_id").entityId()
    val name = varchar("name", 255)
    val email = varchar("email", 255).uniqueIndex()
    val passwordHash = varchar("password_hash", 60)
}

object RefreshTokens: IdTable<UUID>() {
    override val id = uuid("refresh_token_id").entityId()
    val userId = reference("user_id", Users).uniqueIndex()
    val token = varchar("token", 255)
    val expiresAt = datetime("expires_at")
}

I am connecting to a PostgreSQL database and attempting to create the schema with the following function:

fun Application.configureDatabase() {
    Database.connect(
        "jdbc:postgresql://localhost:5432/ktordb",
        driver = "org.postgresql.Driver",
        user = "root",
        password = "1234"
    )

    transaction {
        SchemaUtils.create(Users)
        SchemaUtils.create(RefreshTokens)
    }
}

However, when I run this, I get an error indicating there is no unique constraint matching the keys for the referenced table "users".

The error message:

ERROR: there is no unique constraint matching given keys for referenced table "users"

I have tried making the ‘id’ column in the Users table a primary key and unique, but the error persists. Could anyone provide some insight into what I’m doing wrong here?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Fixed by adding this line to User object:

    override val primaryKey = PrimaryKey(id, name = "PK_USER_ID")

  2. 0

    Try it

    object Users : UUIDTable(name = "user_test", columnName = "user_id") {
    val name = varchar("name", 255)
    val email = varchar("email", 255).uniqueIndex()
    val passwordHash = varchar("password_hash", 60)
}

object RefreshTokens: UUIDTable(name = "token_test", columnName = "refresh_token_id") {
    val userId = reference("user_id", Users).uniqueIndex()
    val token = varchar("token", 255)
    val expiresAt = datetime("expires_at")
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search