Liquibase creates the relationships well but Hibernate doesn't in my integration tests with Spring Boot

Question

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.

Answer 1

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

Previous:

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

Working one:

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