Composite Primary Key

Composite Primary Keys: Database and JPA Mapping 🗝️

📌 Overview

This document explains composite primary keys in the context of database design and how to properly map them in Spring Boot using JPA (Java Persistence API). You'll learn when to use them, how they work in the database, and the two primary strategies for mapping them in your entity classes.

A composite primary key (or compound key) is a unique identifier made up of two or more columns. In this project, the hrs.user_bookmarks table uses a composite key of user_id and reporting_unit_id—allowing users to bookmark multiple reporting units while preventing duplicate bookmarks.

🤔 Why Composite Keys?

Composite keys shine when you need to capture a many-to-many relationship with additional constraints:

• A user can bookmark many reporting units
• A reporting unit can be bookmarked by many users
• But the same user can't bookmark the same unit twice

Without a composite key, you'd need a separate unique constraint, which is messier and less semantically clear.

---

What is a Composite Primary Key?

A composite primary key is a primary key made of two or more columns.

In this project, hrs.user_bookmarks uses:

• user_id
• reporting_unit_id

Together, they uniquely identify one row.

💾 Database Behavior

From src/main/resources/db/migration/V1.0.1__bookmarks.sql:

CREATE TABLE IF NOT EXISTS hrs.user_bookmarks (
    user_id           VARCHAR(70)    NOT NULL,
    reporting_unit_id DECIMAL(12,0)  NOT NULL,
    CONSTRAINT user_bookmarks_pk PRIMARY KEY (user_id, reporting_unit_id)
);

What This Guarantees

The composite primary key enforces several database-level constraints:

• ✅ A user can bookmark many reporting units
• ✅ A reporting unit can be bookmarked by many users
• ✅ The pair (user_id, reporting_unit_id) can appear only once (uniqueness guaranteed)
• ✅ NeitherExamples

-- ✅ OK - First bookmark for user
INSERT INTO hrs.user_bookmarks (user_id, reporting_unit_id)
VALUES ('abc123', 1001);

-- ✅ OK - Same user, different unit (different pair)
INSERT INTO hrs.user_bookmarks (user_id, reporting_unit_id)
VALUES ('abc123', 1002);

-- ❌ FAILS - Duplicate composite key (same user-unit pair)
INSERT INTO hrs.user_bookmarks (user_id, reporting_unit_id)
VALUES ('abc123', 1001);
-- Result: Unique constraint violation

---

🔗 Mapping in Spring Boot JPA

Now that we understand composite keys at the database level, we need to represent them in our Java entities. Spring Data JPA provides two primary strategies:

1. @IdClass — Use a separate class to hold the composite key fields
2. @EmbeddedId — Use an embedded object (@Embeddable) as the key

This project uses @IdClass, which is simpler and more straightforward when your key fields are primitive types (strings, numbers, etc.).

📚 Reference: See the Spring Data JPA documentation on composite keys for detailed comparisons of both approaches.

Strategy 1: @IdClass (Our Approach)

Entity Class

In src/main/java/ca/bc/gov/nrs/hrs/entity/users/UserBookmarkEntity.java:

@Entity
@Table(name = "user_bookmarks", schema = "hrs")
@IdClass(UserBookmarkEntityId.class)
@Data
@Builder
@With
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(onlyExplicitlyIncluded = true)
@EntityListeners(AuditingEntityListener.class)
public class UserBookmarkEntity {

  @Id
  @Column(name = "user_id", nullable = false, length = 70)
  @EqualsAndHashCode.Include
  private String userId;

  @Id
  @Column(name = "reporting_unit_id", nullable = false)
  @EqualsAndHashCode.Include
  private Long reportingUnitId;
}

Key annotations:

• @IdClass(UserBookmarkEntityId.class) — Tells JPA this entity uses a composite key
• @Id — Marks each field that's part of the composite key
• @EqualsAndHashCode.Include — Ensures Lombok only generates equality based on the key fields (not other properties)

ID Class Requirements

In src/main/java/ca/bc/gov/nrs/hrs/entity/users/UserBookmarkEntityId.java:

• Must implement Serializable — Required by the JPA specification for all composite key classes
• Must contain the same field names and types as the @Id fields on the entity
• Needs a no-args constructor — JPA instantiates it during deserialization
• Must have stable equals/hashCode — Lombok @Data covers this.

ID Class Implementation

src/main/java/ca/bc/gov/nrs/hrs/entity/users/UserBookmarkEntityId.java:

@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserBookmarkEntityId implements Serializable {

  private static final long serialVersionUID = 1L;

  private String userId;
  private Long reportingUnitId;
}

Why Serializable? The JPA specification requires composite key classes to implement Serializable because the persistence provider may need to serialize the key for second-level caching, distributed sessions, or other provider-specific features. It's a safeguard even when not immediately necessary.

---

🔍 Working with Composite Keys in Repositories

With a composite key, the repository's second type parameter is the ID class, not the individual column type:

@Repository
public interface UserBookmarkRepository
    extends PagingAndSortingRepository<UserBookmarkEntity, UserBookmarkEntityId>,
            CrudRepository<UserBookmarkEntity, UserBookmarkEntityId> {

  /**
   * Returns all bookmarks belonging to a given user.
   * This is a derived query method — simpler than using the full ID.
   */
  List<UserBookmarkEntity> findByUserId(String userId);
}

Lookup, Exist, and Delete Examples

// ✅ Lookup by composite key
UserBookmarkEntityId id = new UserBookmarkEntityId(userId, reportingUnitId);
Optional<UserBookmarkEntity> bookmark = repository.findById(id);

// ✅ Check existence
boolean exists = repository.existsById(
  new UserBookmarkEntityId(userId, reportingUnitId)
);

// ✅ Delete by composite key
repository.deleteById(new UserBookmarkEntityId(userId, reportingUnitId));

// ✅ Find all bookmarks for a user (derived query method)
// No need to construct the full ID—this method is simpler
List<UserBookmarkEntity> bookmarks = repository.findByUserId(userId);

---

📊 @IdClass vs @EmbeddedId: Comparison

• Field name mismatch — Entity fields must exactly match the ID class field names and types
• Missing Serializable — The ID class must implement Serializable
• Missing no-args constructor — JPA needs to instantiate the ID class during deserialization
• Forgetting @EqualsAndHashCode.Include — When using onlyExplicitlyIncluded = true on the entity, both key fields must be marked, otherwise equality becomes unreliable
• Single-column uniqueness assumption — Remember: only the pair is unique, not individual columns
• @EmbeddedId conflicts — Don't mix @IdClass and @EmbeddedId on the same entity (JPA requires just one)

---

🎯 Practical Summary for user_bookmarks

The real identity of any user bookmark row is the pair (user_id, reporting_unit_id)—not either column individually. Here's what that means in practice:

1. Database Level: The PRIMARY KEY constraint prevents duplicate pairs
2. JPA Level: We use @IdClass(UserBookmarkEntityId.class) to tell Spring how to manage this composite identity
3. Repository Level: Methods querying by the full ID must construct a UserBookmarkEntityId object
4. Derived Queries: Simpler queries like findByUserId() still work—JPA handles the partial key implicitly

---

📚 Additional References

• Spring Data JPA Official Docs: https://spring.io/projects/spring-data-jpa
• JPA Specification (Composite Keys): https://docs.oracle.com/cd/E11035_01/isv/obrien_ch04.pdf
• Hibernate Documentation (@IdClass): https://docs.jboss.org/hibernate/orm/current/userguide/html_single/Hibernate_User_Guide.html#identifiers-composite
• Baeldung Guide: https://www.baeldung.com/jpa-composite-primary-keys

---

⚠️ Common Pitfalls

• Field name or type mismatch between the entity and the ID class.
• Missing Serializable on the ID class.
• Missing no-args constructor in the ID class.
• Forgetting @EqualsAndHashCode.Include on both key fields when using onlyExplicitlyIncluded = true.
• Assuming one key column is unique by itself when only the pair is unique.

7) Practical summary for user_bookmarks

• The real identity of a row is the pair (user_id, reporting_unit_id).
• In JPA, this is represented by UserBookmarkEntityId used with @IdClass.
• Repository methods that target a single row must use both key values, either via the ID class or derived query method names.