PSQLException: ERROR: insert or update on table "expenses" violates foreign key constraint "expenses_user_id_fkey"

Question

I am working on simple Spring Boot service with Kotlin, and PostgreSql. I use JPA. Currently I have two tables "users", and "expenses", "expenses" table have reference to "users" id:

CREATE TABLE users (
    id BIGINT NOT NULL PRIMARY KEY,
    username VARCHAR NOT NULL,
    password VARCHAR NOT NULL
);


CREATE TABLE expenses (
    id BIGINT NOT NULL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    price DECIMAL NOT NULL,
    title VARCHAR NOT NULL,
    description VARCHAR
);

Entities look like this:

@Entity
data class Users (
        @Id @GeneratedValue( strategy = GenerationType.AUTO ) val id: 
Long,
        val username: String,
        var password: String
) {
    @OneToMany
    val expenses: MutableSet<Expenses> = HashSet()
}

@Entity
data class Expenses(
        @Id @GeneratedValue( strategy = GenerationType.AUTO ) val id: 
Long? = null,
        val user_id: Long,
        val price: BigDecimal? = null,
        val title: String? = null,
        val description: String? = null
) {
    @ManyToOne
    lateinit var users: Users

    constructor(user_id: Long, users: Users) : this(user_id = user_id) {
        this.users = users
    }
}

I use JPA repository to connect to database, and CrudRepository to insert data into database:

interface ExpensesRepository : CrudRepository<Expenses, Long> {
}

Heres the code I use for inserting data into db:

@PostMapping("/expenses")
@ResponseBody
fun insertExpense(@RequestBody expenses: Expenses): Expenses {
    expensesRepository.save(expenses);
    return expenses;
}

And JSON I send to "/expenses" with method post:

{
    "user_id:": 43,
    "price": 10.99,
    "title": "C++ course",
    "description": "One time pay"
}

And service throws PSQLException:

org.postgresql.util.PSQLException: ERROR: insert or update on table "expenses" violates foreign key constraint "expenses_user_id_fkey"
  Detail: Key (user_id)=(0) is not present in table "users".
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2477) ~[postgresql-42.1.4.jar:42.1.4]
...

There is some answers in stackoverflow with the same error, bug technology stack are different.

I use gradle. Java version: 1.8 Kotlin version: 1.2.51 Spring Boot version: 2.0.6.RELEASE PostgreSql version: 9.5.10 PostgreSql driver version: 42.1.4

score 1 · Accepted Answer · answered Oct 27 '18 at 20:36

You should be setting the object of user in your expenses object. The relationship seems to be broken as in the error you are getting Key (user_id)=(0) not (43) which is the number you entered. Try below solution. https://grokonez.com/spring-framework/spring-data/kotlin-springjpa-hibernate-one-many-relationship

@Entity
data class Users (
        @Id @GeneratedValue( strategy = GenerationType.AUTO ) val id: 
Long,
        val username: String,
        var password: String
) {
    @OneToMany
     @OneToMany(mappedBy = "users", cascade = arrayOf(CascadeType.ALL), fetch = FetchType.EAGER)
    val expenses: MutableSet<Expenses> = HashSet()
}

@Entity
data class Expenses(
        @Id @GeneratedValue( strategy = GenerationType.AUTO ) val id: 
Long? = null,
        val user_id: Long,
        val price: BigDecimal? = null,
        val title: String? = null,
        val description: String? = null
) {
    @ManyToOne
    lateinit var users: Users
    @JoinColumn(name = "user_id")
    constructor(user_id: Long, users: Users) : this(user_id = user_id) {
        this.users = users
    }
}

From user_id you are putting in input, you can first find the user and then set the user object in the expenses and then try to save it.

Hi thank you for helping! The link you . provided helped me :) Problem was in `val`. I changed from `val` to `var`. [Here](https://stackoverflow.com/a/44200189/5834613) says that `val` is same as final in Java — Domas A., Oct 28 '18 at 08:54

PSQLException: ERROR: insert or update on table "expenses" violates foreign key constraint "expenses_user_id_fkey"

1 Answers1