Why do I get a java.sql.SQLIntegrityConstraintViolationException from a Hibernate many to many relation (foreign key constraint)

Question

I have tbl_books which has m2m with tbl_author. I want to save a book and its authors. I'm able to save the book if I don't include a list of authors but how am I suppose to save the authors to the book? Do I need to create the tbl_book_authors table in order to achieve this?

{
    "title": "a new book",
    "publisher": {
        "id": 7
    },
    "authors": [
        {
            "id": 3
        }
    ]
}

Book.java

...
@NoArgsConstructor
@Entity(name="Book")
@Table(name="tblBook")
public class Book {

    @Id
    @Column(name = "id", unique = true, nullable = false)
    private int id;
    private String title;
    @ManyToOne(fetch = FetchType.EAGER, optional = false)
    @JoinColumn(name = "pub_id", nullable = false)
    private Publisher publisher;
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "tblBookAuthors",
            joinColumns = { @JoinColumn(name = "bookId") },
            inverseJoinColumns = { @JoinColumn(name = "authorId") })
    private Set<Author> authors = new HashSet<>();
    ...

author.java

...
@NoArgsConstructor
@Entity(name="Author")
@Table(name="tblAuthor")
public class Author {

    @Id
    @Column(name = "id")
    private int id;
    @Column(name = "name")
    private String name;
    @JsonBackReference
    @ManyToMany(mappedBy = "authors")
    Set<Book> books;

    ...

log

Book{id=0, title='a new book', publisher=Publisher{id=7, name='null', address='null', phone='null', books=null}, authors=[Author{id=3, name='null', books=null}]}
Hibernate: select publisher_.id, publisher_.address as address2_4_, publisher_.name as name3_4_, publisher_.phone as phone4_4_ from tbl_publisher publisher_ where publisher_.id=?
Hibernate: insert into tbl_book (pub_id, title, id) values (?, ?, ?)
Hibernate: insert into tbl_book_authors (book_id, author_id) values (?, ?)
2021-11-27 18:49:06.643  WARN 8704 --- [nio-8080-exec-7] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1452, SQLState: 23000
2021-11-27 18:49:06.643 ERROR 8704 --- [nio-8080-exec-7] o.h.engine.jdbc.spi.SqlExceptionHelper   : Cannot add or update a child row: a foreign key constraint fails (`library`.`tbl_book_authors`, CONSTRAINT `fk_tbl_book_authors_tbl_book1` FOREIGN KEY (`book_id`) REFERENCES `tbl_book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
2021-11-27 18:49:06.644  INFO 8704 --- [nio-8080-exec-7] o.h.e.j.b.internal.AbstractBatchImpl     : HHH000010: On release of batch it still contained JDBC statements
2021-11-27 18:49:06.663 ERROR 8704 --- [nio-8080-exec-7] 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.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement] with root cause

java.sql.SQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`library`.`tbl_book_authors`, CONSTRAINT `fk_tbl_book_authors_tbl_book1` FOREIGN KEY (`book_id`) REFERENCES `tbl_book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

Answer 1

Either declare Primary Key with auto increment or pass primary from json request. If you are not doing like that, The primary key is stored with 0 value in a database.

Declare primary key with @GeneratedValue(strategy = GenerationType.IDENTITY) for auto increment.

public class Book {

    @Id
    @Column(name = "id", nullable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    ...
    ...
}