Question 89613 in PostgreSQL Question posted in
The official documentation can be found here.

Delete entities of first table before deletion entities of second table error. Spring JPA – Postgresql

Can you please help me? I have two entities:

@Entity
public class Site {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    // getters, setters, etc..
}

@Entity
@Table(indexes = @Index(name = "path_index", columnList = "path"))
public class Page {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "site_id", nullable = false)
    private Site site;

    @Column(nullable = false, unique = true, length = 500)

    // getters, setters, etc..
}

And then I try delete list of pages, but getting ERROR: update or delete on table "site" violates foreign key constraint…:

public void deleteIndexes(ConfigSite configSite) {
        Optional<Site> site = siteRepository.findByUrl(configSite.getUrl());
        if (site.isEmpty()) {
            return;
        }
        Optional<List<Page>> pages = pageRepository.findAllBySiteId(site.get().getId());
        pages.ifPresent(pageList -> pageRepository.deleteAll(pageList));
        siteRepository.delete(site.get());
    }

Why am I getting error if I follow order?

I know that I can create list of pages in Site-table and set cascade, but I do not want to contain list of pages in site-table

Tags:

2

Answers


  1. 0

    https://vladmihalcea.com/n-plus-1-query-problem/

    The infamous N+1 problem

    Many ways to deal with it, usually go with the simplest one.

    Login or Signup to reply.
  2. 0

    Maybe the loading takes so long that a new page was inserted between the first loading and committing of the transaction.
    Try using a query like delete from Page p where p.site.id = :siteId instead which doesn’t require loading. There is still a slight chance that a different transaction commits a new page, but then you can only retry the operation.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search