How can I get hibernate to do a unidirectional ManyToOne mapping without using the primary key?

Question

We have a table structure like this:

CREATE TABLE `client` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `member` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `key` varchar(255) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`key`)
);

CREATE TABLE `client_member` (
  `client_id` bigint NOT NULL,
  `member_key` varchar(255),
  UNIQUE KEY `client_id` (`client_id`,`member_key`),
  FOREIGN KEY (`client_id`) REFERENCES `client` (`id`),
  FOREIGN KEY (`member_key`) REFERENCES `member` (`key`)
);

We wanted to map the entities like so:

@Entity
@Table(name = "client")
class Client(
    @Id
    @Access(AccessType.PROPERTY)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, nullable = false, updatable = false)
    var id: Long?,

    @Column(name = "name", nullable = false)
    var name: String,

    @ManyToMany
    @JoinTable(
        name = "client_member",
        joinColumns =  [JoinColumn(
            name = "client_id",
            referencedColumnName = "id",
            nullable = false
        )],
        inverseJoinColumns = [JoinColumn(
            name = "member_key",
            referencedColumnName = "key",
            nullable = false
        )]
    )
    var clientMembers: MutableSet<Member>,
)

@Entity
@Table(name = "member")
class Member(
    @Id
    @Access(AccessType.PROPERTY)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, nullable = false, updatable = false)
    var id: Long?,

    @NaturalId
    @Column(name = "key", unique = true, nullable = false, updatable = false)
    var key: String,

    @Column(name = "name", nullable = false)
    var name: String,
)


@Entity
@Table(name = "client_member")
class ClientMember(
    @EmbeddedId
    var id: ClientMemberCompositeKey
) {
    @Embeddable
    class ClientMemberCompositeKey(
        @ManyToOne
        @JoinColumn(
            name = "client_id",
            referencedColumnName = "id",
            nullable = false
        )
        var client: Client,

        @ManyToOne
        @JoinColumn(
            name = "member_key",
            referencedColumnName = "key",
            nullable = false
        )
        var member: Member,
    )
}

But when we tried to use these mappings and save Client with a new Member, this error occurs:

org.hibernate.engine.jdbc.spi.SqlExceptionHelper: Cannot add or update a child row: a foreign key constraint fails (client_member, CONSTRAINT client_member_ibfk_1 FOREIGN KEY (member_key) REFERENCES member (key))

After debugging, we found that this is what hibernate was trying to execute:

INSERT INTO `client_member` (`client_id`, `member_key`) VALUES (14, 183)

183 is the id of the member, but the key is c90bcfd6-86be-4ec4-8e16-2a541ce87317. It seems that Hibernate is ignoring the referencedColumnName and is still trying to pass in the id instead of the key of the member.

Is there any way I can remedy this without having to add a mapping of clients to the member entity making this bidirectional? We'd like to keep it unidirectional if possible

Answer 1

The issue here is that Hibernate, by default, wants to use the primary key (id) when handling @ManyToOne relationships, even though you told it to reference key. The referencedColumnName = "key" in @JoinColumn only affects the SQL schema, not how Hibernate actually fetches or persists data. So, Hibernate is still trying to insert member.id into client_member instead of member.key, which causes the foreign key constraint error.

Since you want Hibernate to use member.key instead of id, you need to tweak how you're handling the client_member table. The best way is to manually define a composite key using @Embeddable and ensure that member_key is just a String rather than a full @ManyToOne mapping.