Who should be parent in spring JPA relationship

Question

I have 3 tables book, publisher, author. Here author and publisher can refer to same record in book therefore I decided to make book as relationship parent.

However I also want to make it so that if a publisherA is deleted, all the book record related to publisherA also get deleted. same goes for author.

I tried using cascade, but it ended up doing the opposite, if I delete a book, it would delete any publisher and author related to the book.

Here are my Tables

create table publisher(
    `PUBLISHER_ID` int auto_increment,
    `PUBLISHER_NAME` varchar(150),
    `CONTACT_NO` varchar(10),
    `EMAIL` varchar(30),
    `ADDRESS_LINE_1` varchar(255),
    `ADDRESS_LINE_2` varchar(255),
    `CITY` varchar(50),
    `STATE` varchar(50),
    `PINCODE` varchar(6),
    primary key(PUBLISHER_ID)
    );
    
create table author_book(
    `AUTHOR_ID` INT,
    `BOOK_ID` INT
);

create table author(
    `AUTHOR_ID` int auto_increment,
    `FIRST_NAME` varchar(255),
    `LAST_NAME` varchar(255),
    `EMAIL` varchar(30),
    `CONTACT_NO` VARCHAR(10),
    primary key(AUTHOR_ID)
);
create table book(
    `BOOK_ID` int auto_increment,
    `TITLE` varchar(255),
    `SUBJECT` varchar(255),
    `PUBLISHED_YEAR` int,
    `ISBN` varchar(30),
    `QUANTITY` int,
    `SHELF_DETAILS` varchar(255),    
    `PUBLISHER_ID` int,
    `BOOK_COST` INT,
    primary key(BOOK_ID)
);

author Entity

@Entity
@Table(name="author")
public class Authors {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="AUTHOR_ID")
    private int authorId;
    
    @Column(name="FIRST_NAME")
    private String firstName;
    
    @Column(name="LAST_NAME")
    private String lastName;
    
    @Column(name="EMAIL")
    private String email;
    
    @Column(name="CONTACT_NO")
    private String contactNo;
    
    @JsonIgnore
    @ManyToMany(fetch = FetchType.LAZY, mappedBy = "authors")
    private Set<Books> books = new HashSet<Books>();

    //getter setter
}

publisher Entity

package com.sprint.proj.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "publisher")
public class Publishers {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="PUBLISHER_ID")
    private int publisherId;
    
    @Column(name="PUBLISHER_NAME")
    private String publisherName;
    
    @Column(name="CONTACT_NO")
    private String contactNo;
    
    @Column(name="EMAIL")
    private String email;
    
    @Column(name="ADDRESS_LINE_1")
    private String address1;
    
    @Column(name="ADDRESS_LINE_2")
    private String address2;
    
    @Column(name="CITY")
    private String city;
    
    @Column(name="STATE")
    private String state;
    
    @Column(name="PINCODE")
    private int pincode;
// getter and setters
}

book Entity

@Entity
@Table(name = "book")
public class Books implements Serializable{
    private static final long serialVersionUID = 1L;
    
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="BOOK_ID")
    private int bookId;
    
    @Column(name="TITLE")
    private String title;
    
    @Column(name="SUBJECT")
    private String subject;
    
    @Column(name="PUBLISHED_YEAR")
    private int publishedYear;
    
    @Column(name="ISBN")
    private String isbn;
    
    @Column(name="QUANTITY")
    private int quantity;
    
    @Column(name="SHELF_DETAILS")
    private String shelfDetails;
    
    @Column(name="BOOK_COST")
    private int bookCost;
    
    @JsonIgnore
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "author_book", 
            joinColumns = { @JoinColumn(name = "BOOK_ID") }, 
            inverseJoinColumns = { @JoinColumn(name = "AUTHOR_ID") })
    private Set<Authors> authors = new HashSet<Authors>();
    
    @JsonIgnore
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="PUBLISHER_ID")
    private Publishers publisher;
// getter and setters
}

What should happen if a book has multiple authors and you delete only one of them? — sp00m, Jun 11 '21 at 15:23

sp00m · Accepted Answer · 2021-06-11T15:25:48.653

This can be done at MySQL level via proper foreign key constraints, for example for the book/publisher relationship:

alter table book
add constraint fk_book_publisher_id
  foreign key (PUBLISHER_ID)
  references publisher(PUBLISHER_ID)
  -- no action on update,
  -- unless you want to allow publisher's PK (PUBLISHER_ID) to be updated
  -- (but I guess not):
  on update no action
  -- when the referenced record is deleted
  -- (so the record from table publisher),
  -- delete the referencing records too
  -- (so the records from table book):
  on delete cascade;

Concerning the book/author relationship though, your model seems to allow one book to have mutliple authors (and one author multiple books), hence the author_book join table in the middle. In this case, FKs won't allow cascading deletes from author to book unfortunately (they would leave books "author-less"). This will need to be done by the app (perhaps doable by JPA). Although, what should happen if a book has multiple authors and you delete only one of them?

score 1 · Answer 2 · answered Jun 11 '21 at 13:22

@JsonIgnore
@ManyToMany(fetch = FetchType.LAZY, mappedBy = "authors" , cascade = Cascade.ALL)
private Set<Books> books = new HashSet<Books>();

@JsonIgnore
@ManyToMany(fetch = FetchType.LAZY) --> ignore cascade here so it will be none
@JoinTable(name = "author_book", 
        joinColumns = { @JoinColumn(name = "BOOK_ID") }, 
        inverseJoinColumns = { @JoinColumn(name = "AUTHOR_ID") })
private Set<Authors> authors = new HashSet<Authors>();

Actually you can use author repository to delete Book with cascade all but if you delete with book repository you won't delete author because it is ignored in @ManyToMany relation. In two cases relation table's record is always deleted.

Also you don't need to specify on update or on delete actions in database script.

score 0 · Answer 3 · answered Jun 11 '21 at 12:54

I would like recommend to soft delete manually via update.

https://blog.staynoob.cn/post/2019/05/when-soft-delete-meets-unique-index/

Then add where clause to entity to get only not deleted entities from repository

Handling soft-deletes with Spring JPA

Who should be parent in spring JPA relationship

3 Answers3