JPA Secondary table causes error cause of Foreign Key restriction

Question

So I have two tables:

Create Table annotation_table (Id varchar(255), Hash varchar(255), PRIMARY KEY (Id, Hash));

Create Table commit_table (Id varchar(255), Hash varchar(255), commit_Id varchar(255), PRIMARY KEY (Id, Hash), FOREIGN KEY (Id, Hash) references annotation_table (Id, Hash));

Note that both tables have been reduced to their basic values for simplification reasons. Both tables are mapped to a single Entity (also simplified):

@Table(name = "annotation_table")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@SecondaryTables({
        @SecondaryTable(name = "commit_table", pkJoinColumns = {@PrimaryKeyJoinColumn(name="Id"), @PrimaryKeyJoinColumn(name="Hash")})
})
@IdClass(Key.class)
public class Annotation {

    @Id
    @Column(name = "Id")
    private String id;

    @Id
    @Column(name = "Hash")
    private String hash;

    @Column(name = "commit_id", table = "commit_table")
    private String commitHash;

}

I Use a repository that incorporates following save method:

    @Override
    public void save(Annotation annot) {
        em.getTransaction().begin();
        annot = em.merge(annot);
        em.persist(annot);
        em.getTransaction().commit();

    }

Everytime I try to create an Annotation and save it, I get following error:

ERROR: Cannot add or update a child row: a foreign key constraint fails (db_schema.commit_table, CONSTRAINT commit_table_ibfk_1 FOREIGN KEY (Id, Hash) REFERENCES annotation_table (Id, Hash))

The error is caused because JPA tries to insert first into "commit_table" and then into "annotation_table".

How can I tell JPA to flip the query order or to automatically do its job?

score 0 · Answer 1 · answered Jul 06 '22 at 11:09

Ok so i figured it out. The Error was caused by the missing "referencedColumnName = " statement in the secondarytable annotation. The fixed code looks like this:

@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@SecondaryTables({
        @SecondaryTable(name = "commit_table", pkJoinColumns = {@PrimaryKeyJoinColumn(name="Id", referencedColumnName = "Id"), @PrimaryKeyJoinColumn(name="Hash", referencedColumnName = "Hash")})
})
@IdClass(Key.class)
public class Annotation {

    @Id
    @Column(name = "Id")
    private String id;

    @Id
    @Column(name = "Hash")
    private String hash;

    @Column(name = "commit_id", table = "commit_table")
    private String commitHash;

}

JPA Secondary table causes error cause of Foreign Key restriction

1 Answers1