Liquibase creates the relationships well but Hibernate doesn't in my integration tests with Spr-CodePudding

I'm running an embedded postgres DB for integration tests in Spring Boot.

The idea is to disable liquibase for integration tests since it populates a lot of unnecessary data for testing in our project. With liquibase enabled the test works but I tried out this approach:

spring:
  liquibase:
    enabled: false
  jpa:
    hibernate:
      ddl-auto: create-drop
    show-sql: true

And Hibernate creates the relationships but they fail if you leave null one end of the relation.

This is the error I get:

ERROR: insert or update on table "faq" violates foreign key constraint "fkponmkmbropnkmq3y1juw9v44"
  Detail: Key (id)=(1) is not present in table "faq_subcategory".

This is the relation in the Faq entity:

    @ManyToOne(optional = true, fetch = FetchType.LAZY)
    @JoinColumn(name = "subcategory_id", nullable = true)
    @JsonIgnore
    private FAQSubcategory faqSubcategory;

This is the relation in the FaqSubcategory entity:

    @OneToMany(mappedBy = "id", fetch = FetchType.LAZY, orphanRemoval = false)
    private List<Faq> faqs;

optional = true and nullable = true are redundant but I put it there just in case but still get the same error. I'm not sure what am I missing.

One subcategory can have many faqs and each faq can belong to a subcategory but It doesn't need to. If I run an INSERT statement directly on the postgres DB with the subcategory_id = null it doesn't complain.

Any thoughts?

Thanks.

EDIT 1:

Following @Gleb Yan's advice, I've compared both schemas.

When I create it with liquibase the DDL is:

CREATE TABLE public.faq (
    id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    creation_date timestamp(6) NULL,
    creator_id varchar(255) NULL,
    modification_date timestamp(6) NULL,
    modifier_id varchar(255) NULL,
    "name" varchar(255) NULL,
    order_int int4 NULL,
    active bool NULL,
    subcategory_id int8 NULL,
    CONSTRAINT faq_pkey PRIMARY KEY (id),
    CONSTRAINT fk_faq_faq__subcategory FOREIGN KEY (subcategory_id) REFERENCES public.faq_subcategory(id)
);

But with hibernate.ddl-auto=create-drop it creates two foreign keys:

CREATE TABLE public.faq (
    id bigserial NOT NULL,
    creation_date timestamp NULL,
    creator_id varchar(255) NULL,
    modification_date timestamp NULL,
    modifier_id varchar(255) NULL,
    active bool NULL,
    "name" varchar(255) NULL,
    order_int int4 NULL,
    subcategory_id int8 NULL,
    CONSTRAINT faq_pkey null,
    CONSTRAINT fkoqpq35oftfpexvtkutja0p4ab FOREIGN KEY (subcategory_id) REFERENCES public.faq_subcategory(id),
    CONSTRAINT fkponmkmbropnkmq3y1juw9v44 FOREIGN KEY (id) REFERENCES public.faq_subcategory(id)
);

Why is hibernate doing this?

EDIT 2:

I've tried out to change the annotations to this:

    @ManyToOne(targetEntity = FAQSubcategory.class)
    @JsonIgnore
    private FAQSubcategory faqSubcategory;

    @OneToMany(targetEntity = FAQ.class, fetch = FetchType.LAZY)
    private List<FAQ> faqs;

And now hibernate does the DDL correctly with only 1 foreign key but if I turn on liquibase again it doesn't work.

Caused by: org.postgresql.util.PSQLException: ERROR: column "faq_subcategory_id" of relation "faq" does not exist

I can't get my head around it.

CodePudding user response：

Finally found it. I had the subcategory mapped by the id of the faq instead of the subcategoryId field.

    @OneToMany(mappedBy = "id", fetch = FetchType.LAZY, orphanRemoval = false)
    private List<Faq> faqs;

Working one:

    @OneToMany(mappedBy = "faqSubcategory", fetch = FetchType.LAZY)
    private List<FAQOKHelp> faqs;