Spring Boot Data JDBC: Mapping 1 to 1 relationship and persisting in kotlin

Question

Hello together I am currently trying to persist an entity to the database with a 1 to 1 relationship.

I am using spring version 2.5.3 (spring-boot-data-jdbc). When I try to persist via rest post and the usual crud repository save() method with this json.

{
    "name": "everyday at 15",
    "announcement": {
        "name": "This is the third announcement"
    }
}

I get this error message:

2021-08-19 14:20:06.315 ERROR 7946 --- [nio-8080-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.data.relational.core.conversion.DbActionExecutionException: Failed to execute DbAction.InsertRoot(entity=Timetable(id=null, name=everyday at 15, announcement=Announcement(id=null, name=This is the third announcement)))] with root cause

org.postgresql.util.PSQLException: ERROR: insert or update on table "timetable" violates foreign key constraint "fk_announcement"
  Detail: Key (announcement)=(6) is not present in table "announcement".

It seems like the counter for the announcement id is always going up however it never reaches the point where anything is persisted. My entity and db setup are listed below.

@Table("announcement")
data class Announcement(
    @Id
    val announcement_id: Long?,
    val name: String
)

@Table("timetable")
data class Timetable(
    @Id
    var id: Long?,
    val name: String,
    val announcement: Announcement
)

CREATE TABLE announcement(
    announcement_id serial,
    name            varchar(30) not null,
    PRIMARY KEY(id)
);

CREATE TABLE timetable(
    id              serial,
    name            varchar(30) not null,
    announcement_id serial,
    PRIMARY KEY(id),
    CONSTRAINT fk_announcement
        FOREIGN KEY (announcement_id)
            REFERENCES announcement (announcement_id)
);

Thank you for your help!

“and the usual crud repository save() method” — can you include the relevant code, showing how you create and try to persist your Announcement and Timetable? — gidds, Aug 19 '21 at 12:57
Maybe a missing one-to-one annotation on the timetable announcement parameter you had wrong mappedBy value in Image - mappedBy = "images" it should be "image": @OneToOne(mappedBy = "...", cascade = CascadeType.ALL, fetch= FetchType.EAGER) ? — Marek Żylicz, Aug 19 '21 at 13:08

score 0 · Answer 1 · answered Aug 25 '21 at 07:11

Since Timetable is your aggregate root, the foreign key should be placed on the announcement table, not on the timetable table.

Note that Announcement.announcement_id is superfluous as far as Spring Data JDBC is concerned. In the following schema I left it in but put the primary key on the new timetable column:

CREATE TABLE announcement(
    announcement_id serial,
    timetable biginteger, -- the correct type to use depends on the database you use.
    name            varchar(30) not null,
    PRIMARY KEY(timetable),
    CONSTRAINT fk_announcement_timetable
        FOREIGN KEY (timetable)
            REFERENCES timetable (announcement_id)
);

CREATE TABLE timetable(
    id              serial,
    name            varchar(30) not null,
    PRIMARY KEY(id)
);

Spring Boot Data JDBC: Mapping 1 to 1 relationship and persisting in kotlin

1 Answers1