Getting "org.h2.jdbc.JdbcSQLException: Table "BOOK_PUBLISHERS" not found; SQL statement:"

0
votes

Using Spring Boot, with Spring Data JPA and H2 in-memory database for process of mapping a many-to-many relationship.

I have two table with entities Book & Publisher. where they many to many relations ship

Created joining table to store the Book_Id and Publisher_id as foreign keys in book_publisher table as below.

create table book_publisher 
(
  book_id number not null,
  publisher_id number not null,
  PRIMARY KEY (book_id, publisher_id),
  CONSTRAINT fk_bookpublisher_book FOREIGN KEY (book_id) REFERENCES CURRENCY (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_bookpublisher_publisher FOREIGN KEY (publisher_id) REFERENCES VENUE (id) ON DELETE CASCADE ON UPDATE CASCADE
 );

I did not write the domain class for the book_publisher table.

But i am getting:

org.h2.jdbc.JdbcSQLException: Table "BOOK_PUBLISHERS" not found; SQL statement: insert into book_publisher (book_id, publisher_ID) values (?, ?) [42102-191] " while storting book object(where book class has publisher objects as "SET" variable

How to solve this error?

1
javaspring-bootmany-to-manyh2

1 Answers

0
votes

If you're running the creation script you mentioned, it'll create a many_to_many relationship between the tables CURRENCY and VENUE. So I would say the code snippet you shared is wrong and this might be why the table BOOK_PUBLISHER was not created successfully.

The right SQL code to create many_to_many relationship (between the tables BOOK and PUBLISHER) should be:

create table book_publisher 
(
  book_id number not null,
  publisher_id number not null,
  PRIMARY KEY (book_id, publisher_id),
  CONSTRAINT fk_bookpublisher_book FOREIGN KEY (book_id) REFERENCES BOOK (id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_bookpublisher_publisher FOREIGN KEY (publisher_id) REFERENCES PUBLISHER (id) ON DELETE CASCADE ON UPDATE CASCADE
 );