[FIXED] HIBERNATE with overlapping composite PRIMARY and FOREIGN KEYs

Issue

I have some entities:

@Entity
@Table(name = "cbonus")
public class BonusEntity {
    @EmbeddedId
    @AttributeOverride(name = "id", column = @Column(name = "id"))
    @AttributeOverride(name = "clusterId", column = @Column(name = "cluster_id"))
    private BonusId bonusId;
    private boolean released;
    
    @ManyToOne(targetEntity = AccountEntity.class, optional = false, fetch = FetchType.LAZY)
    @JoinColumn(name = "cluster_id", referencedColumnName = "cluster_id", insertable = false, updatable = false)
    @JoinColumn(name = "bank_id", referencedColumnName = "bank_id", insertable = false, updatable = false)
    @JoinColumn(name = "user_id", referencedColumnName = "user_id", insertable = false, updatable = false)
    private AccountEntity account;
}

@Entity
@Table(name = "account")
public class AccountEntity {

    @EmbeddedId
    @AttributeOverride(name = "clusterId", column = @Column(name = "cluster_id"))
    @AttributeOverride(name = "bankId", column = @Column(name = "bank_id"))
    @AttributeOverride(name = "userId", column = @Column(name = "user_id"))
    @NotNull
    private AccountId accountId;

    // other fields
}

When I trying to save new cbonus record I have exception:

org.postgresql.util.PSQLException: ERROR: null value in column "bank_id" of relation "cbonus" violates not-null constraint Detail: Failing row contains (773, gp3, null, null, f).

and query

DEBUG 24817 --- [nio-8080-exec-4] org.hibernate.SQL
: insert into cbonus (released, cluster_id, id) values (?, ?, ?)

Just before saving, the object has all fields filled. I tried to remove the null value constraints, but then the values just keep as NULL. I think the reason is overlapping a composite primary key and composite foreign key.

How can I manage this with Hibernate?

db schemas:

CREATE TABLE cbonus
(
    id         BIGINT                NOT NULL,
    cluster_id TEXT                  NOT NULL,
    bank_id    BIGINT                NOT NULL,
    user_id    TEXT                  NOT NULL,
    released   BOOLEAN DEFAULT FALSE NOT NULL,
    FOREIGN KEY (cluster_id, bank_id, user_id) REFERENCES account,
    PRIMARY KEY (id, cluster_id)
);

CREATE TABLE account
(
    cluster_id TEXT       NOT NULL,
    bank_id    BIGINT     NOT NULL,
    user_id    TEXT       NOT NULL,
    -- [other fields]
    PRIMARY KEY (cluster_id, bank_id, user_id)
);

Solution

Your mapping is wrong. The bank_id column is never "writable" according to your settings. You need this:

    @AttributeOverride(name = "id", column = @Column(name = "id"))
    @AttributeOverride(name = "clusterId", column = @Column(name = "pk_cluster_id"))
    private BonusId bonusId;

    @ManyToOne(targetEntity = AccountEntity.class, optional = false, fetch = FetchType.LAZY)
    @JoinColumnsOrFormulas({
       @JoinColumnOrFormula(column = @JoinColumn(name = "bank_id", referencedColumnName = "bank_id")),
       @JoinColumnOrFormula(column = @JoinColumn(name = "user_id", referencedColumnName = "user_id")),
       @JoinColumnOrFormula(formula = @JoinFormula(value = "cluster_id", referencedColumnName = "cluster_id"))
    private AccountEntity account;

    })

and some schema fix:

CREATE TABLE command_response
(
    id                   BIGSERIAL PRIMARY KEY,
    pk_cluster_id        TEXT                    NOT NULL,
    cluster_id           TEXT                    NOT NULL,
    bank_id              BIGINT                  NOT NULL,
    ...
    FOREIGN KEY (cluster_id, bank_id, user_id) REFERENCES account,
    PRIMARY KEY (id, pk_cluster_id)

Answered By - Christian Beikov
Answer Checked By - Willingham (JavaFixing Volunteer)