org.hibernate.MappingException: Foreign key XXX must have same number of columns as the referenced primary key YYY

Question

Having a following SQL table:

create table users_posts_ratings_map (
  postId integer not null references posts (id),
  userId integer not null references users (id),
  ratingId integer not null references ratings (id),
  primary key (postId, userId)
);

and Following JPA-Annotated POJOs:

RatingId.java:

@Embeddable
public class RatingId implements Serializable {
    @ManyToOne
    @JoinColumn(name = "userId")
    private User user;

    @ManyToOne
    @JoinColumn(name = "postId")
    private Post post;

    // getters and setters
}

UserPostRating.java:

@Entity(name = "users_posts_ratings_map")
public class UserPostRating {
    @EmbeddedId
    private RatingId userPost;

    @OneToOne
    @JoinColumn(name = "ratingId")
    private Rating rating;

    // getters and setters
}

Post.java

@Entity(name = "posts")
public class Post {
    @Id
    @Column(nullable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    // irrelevant fields

    @ManyToMany
    @JoinTable(
            name = "users_posts_ratings_map",
            joinColumns = { @JoinColumn(name = "ratingId") },
            inverseJoinColumns = { @JoinColumn(name = "postId"), @JoinColumn(name = "userId") }
    )
    private Set<UserPostRating> ratings = new HashSet<>();

    // getters and setters
}

I am getting

org.hibernate.MappingException: Foreign key (FKB278E73083D94769:users_posts_ratings_map [postId,userId])) must have same number of columns as the referenced primary key (users_posts_ratings_map [ratingId,postId,userId])

on servlet container initialization stage.

What does it mean (What are Foreign Keys in this mappings? What are Primary Keys? Which annotations are marking what?) and how it could be fixed?

Solution 1

This mapping doesn't make much sense. You have an entity UserPostRating, mapped to the users_posts_ratings_map, and having a ManyToOne association with the entity Post.

And in Post, you have a set of UserPostRating, but you map it as a second association, and make it a ManyToMany. It isn't a ManyToMany. It's a OneToMany, since the other side is a ManyToOne. And since the bidirectional association is already mapped in UserPostRating, you can't map it a second time in Post. So the code should be:

@OneToMany(mappedBy="userPost.post")
private Set<UserPostRating> ratings = new HashSet<>();

Solution 2

The Mapping is correct, since it is a many to many mapping so its mapping will result to new table. So you should not refer to existing entity table, rather you should provide any other name whose mapping/entity name does not exist. Below is your example :

 @ManyToMany
    @JoinTable(
            name = "users_posts_ratings_map",
            joinColumns = { @JoinColumn(name = "ratingId") },
            inverseJoinColumns = { @JoinColumn(name = "postId"), @JoinColumn(name = "userId") }
    )
    private Set<UserPostRating> ratings = new HashSet<>();

Change the name from "users_posts_ratings_map" to any other name like users_posts_ratings_map1 or users_posts_ratings_map_item.

Solution 3

According to the error message I suspect, that you have to move the definition of

@OneToOne
@JoinColumn(name = "ratingId")
private Rating rating;

from the class UserPostRating to the class RatingId.