# Sharing column across composite primary and foreign keys

**URL:** <https://discourse.hibernate.org/t/sharing-column-across-composite-primary-and-foreign-keys/9935>\
**Category:** Hibernate ORM\
**Created:** [July 12, 2024, 4:58am UTC](https://discourse.hibernate.org/t/sharing-column-across-composite-primary-and-foreign-keys/9935 "2024-07-12T04:58:54Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![tamzord](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/tamzord/32/1912_2.png) [@tamzord](https://discourse.hibernate.org/u/tamzord)\
**Post date:** [July 12, 2024, 4:58am UTC](https://discourse.hibernate.org/t/sharing-column-across-composite-primary-and-foreign-keys/9935/1 "2024-07-12T04:58:54Z")

</div>

Hello,

Apologies for the long question.

### One-liner (vague)

How do I configure my entities to share a piece of the composite primary key with all foreign keys?

### More Detail

In my application, we segregate users (and their data) in “working sets”.  
This means that every entity has a composite key (workingSetId, entityId).  
Currently, for foreign keys, the working set id column is duplicated, and this works.  
For example:

```sql
CREATE TABLE manager(
    working_set_id UUID,
    entity_id UUID,
    ....
);
CREATE TABLE department(
    working_set_id UUID,
    entity_id UUID,
    manager_working_set_id UUID,
    manager_entity_id UUID,
    ....
);

```

I was wondering if there is a way to reuse the same `working_set_id` column instead of having a `manager_working_set_id` column.  
This is not so much to save data but for the guarantees that come from it:  
no cross-working set references shall exist.

### The Problem

Unfortunately:

- a column can only be insertable/updatable for one field in the entity;
- on composite foreign keys, either all columns or no column must be insertable/updatable.

The only way I found of resolving this was to create a field `managerEntityId` which is insertable/updatable and a field `manager` that is not.  
I am writing exploratory tests to verify this behaves as I intended, but I hit a wall: my update to the manager does not persist (see test below).  
**Q1** : Can you help me understand what is happening behind the scenes?  
**Q2** : If I edit the class field so that the setter explicitly updates the `managerEntityId` field, then the test passes. Is there a way configure the entities such that Hibernate takes care of this?  
**Q3** : Can you show how I could achieve the same on a `@ManyToMany` field? As in, how can I, in the many-to-many table, re-use the same `working_set_id` field for both sides (e.g. SQL below)? I tried the same trick, making all join columns not insertable/updatable and both working set id join columns referring to the same name, but I get an error:

```auto
...
insert into public.department_employees (department_entity_id, working_set_id, employee_entity_id) values (?, ?, ?)
binding parameter [1] as [OTHER] - [1fdcdcb6-0212-45fc-b82c-6d3939849855]
binding parameter [2] as [OTHER] - [f362045d-18df-4d8f-821b-da0a33f7eeea]
binding parameter [3] as [OTHER] - [2261a579-4ce4-4198-8157-4cd9e3277e75]
binding parameter [4] as [OTHER] - [f362045d-18df-4d8f-821b-da0a33f7eeea]
The column index is out of range: 4, number of columns: 3.

```

```sql
CREATE TABLE employee(
    working_set_id UUID,
    entity_id UUID,
    ....
);
CREATE TABLE department_employees(
    working_set_id UUID,
    department_entity_id UUID,
    employee_entity_id UUID,
    ....
)

```

As a difficulty bonus, my setup is to have an abstract `BaseEntity` which stores the ID and is extended by all entity classes.

### The Code

This is my test (Kotlin):

```kotlin
@Tag("exploratory-test")
@SpringBootTest(
    webEnvironment = SpringBootTest.WebEnvironment.NONE,
    classes = [
        EmployeeRepository::class,
        ManagerRepository::class,
        DepartmentRepository::class,
    ]
)
@EntityScan(basePackages = ["mypackage.exploratory.sharedcolumns"])
@EnableJpaRepositories(basePackages = ["mypackage.exploratory.sharedcolumns"])
@TestPropertySource(properties = ["spring.jpa.hibernate.ddl-auto=create-drop", "spring.flyway.enabled=false"])
@ActiveProfiles(resolver = ProfileResolver::class)
@EnableAutoConfiguration
class HibernateSharedWorkingSetIdColumnTest {

    @Autowired
    lateinit var employeeRepository: EmployeeRepository

    @Autowired
    lateinit var managerRepository: ManagerRepository

    @Autowired
    lateinit var departmentRepository: DepartmentRepository

    // THIS TEST PASSES
    @Test
    fun `class with shared workingSetId updates referenced entity when updating referenced entity id`() {
        val workingSetId = randomUUID()
        val manager1 = Manager(workingSetId, "manager1")
        val manager2 = Manager(workingSetId, "manager2")
        val department = Department(workingSetId, "department", managerEntityId = manager1.id!!.entityId)

        managerRepository.save(manager1)
        managerRepository.save(manager2)
        departmentRepository.save(department)

        department.managerEntityId = manager2.id!!.entityId

        departmentRepository.save(department)
        val reFetchedDepartment = departmentRepository.findById(department.id!!).orElseThrow()

        assertAll(
            { assertEquals(manager2.id!!.entityId, reFetchedDepartment.managerEntityId) },
            { assertEquals(manager2.id, reFetchedDepartment.manager!!.id) },
        )
    }

    // BOTH ASSERTIONS FAIL WITHOUT THE EXPLICIT SETTER 
    @Test
    fun `class with shared workingSetId updates referenced entity when updating referenced entity`() {
        val workingSetId = randomUUID()
        val manager1 = Manager(workingSetId, "manager1")
        val manager2 = Manager(workingSetId, "manager2")
        val department = Department(workingSetId, "department", managerEntityId = manager1.id!!.entityId)

        managerRepository.save(manager1)
        managerRepository.save(manager2)
        departmentRepository.save(department)

        department.manager = manager2

        departmentRepository.save(department)
        val reFetchedDepartment = departmentRepository.findById(department.id!!).orElseThrow()

        assertAll(
            { assertEquals(manager2.id!!.entityId, reFetchedDepartment.managerEntityId) },
            { assertEquals(manager2, reFetchedDepartment.manager) },
        )
    }
}

@Embeddable
data class MyCompositeId @JvmOverloads constructor(
    @NotNull
    @Column(nullable = false)
    @GenericGenerator(name = "uuid2", strategy = "uuid2")
    @Type(type = "pg-uuid")
    var workingSetId: UUID? = null,

    @NotNull
    @Column(nullable = false)
    @GenericGenerator(name = "uuid2", strategy = "uuid2")
    @Type(type = "pg-uuid")
    var entityId: UUID? = null
) : Serializable

@MappedSuperclass
abstract class BazeEntity(
    @Id
    @Access(AccessType.PROPERTY)
    var id: MyCompositeId? = null
) : Serializable

@Entity
class Department (
    id: MyCompositeId? = null,

    @Column(nullable = false)
    @field:NotNull
    @NotBlank
    var code: String? = null,

    @Column(name = "manager_entity_id", nullable = false)
    var managerEntityId: UUID? = null,

    @Column(name = "vice_manager_entity_id")
    var viceManagerEntityId: UUID? = null,

    @NotNull
    @ManyToMany(fetch = FetchType.LAZY, cascade = [CascadeType.REFRESH])
    @JoinTable(
        name = "department_employees",
        joinColumns = [
            JoinColumn(name = "department_entity_id", referencedColumnName = "entityId"),
            JoinColumn(name = "department_working_set_id", referencedColumnName = "workingSetId")
        ],
        inverseJoinColumns = [
            JoinColumn(name = "employee_entity_id", referencedColumnName = "entityId"),
            JoinColumn(name = "employee_working_set_id", referencedColumnName = "workingSetId")
        ]
    )
    var employees: MutableSet<Employee> = HashSet()
) : BazeEntity(id) {

    @JoinColumn(name = "workingSetId", referencedColumnName = "workingSetId", insertable = false, updatable = false)
    @JoinColumn(name = "manager_entity_id", referencedColumnName = "entityId", insertable = false, updatable = false)
    @ManyToOne(fetch = FetchType.LAZY, cascade = [CascadeType.REFRESH])
    var manager: Manager? = null
        // Without this, changes are not persisted
        set(value) {
            field = value
            viceManagerEntityId = value?.id?.entityId
        }

    @JoinColumn(name = "workingSetId", referencedColumnName = "workingSetId", insertable = false, updatable = false)
    @JoinColumn(name = "vice_manager_entity_id", referencedColumnName = "entityId", insertable = false, updatable = false)
    @ManyToOne(fetch = FetchType.LAZY, cascade = [CascadeType.REFRESH])
    var viceManager: Manager? = null
        // Without this, changes are not persisted
        set(value) {
            field = value
            viceManagerEntityId = value?.id?.entityId
        }

    constructor(
        workingSetId: UUID? = null,
        code: String? = null,
        managerEntityId: UUID? = null,
        viceManagerEntityId: UUID? = null,
        employees: MutableSet<Employee> = HashSet()
    ) : this(MyCompositeId(workingSetId, randomUUID()), code, managerEntityId, viceManagerEntityId, employees)
}

@Entity
class Manager @JvmOverloads constructor(
    id: MyCompositeId? = null,

    @Column(nullable = false)
    @field:NotNull
    @NotBlank
    var code: String? = null
) : BazeEntity(id) {
    constructor(
        workingSetId: UUID? = null,
        code: String? = null,
    ) : this(MyCompositeId(workingSetId, randomUUID()), code)
}

@Entity
class Employee @JvmOverloads constructor(
    id: MyCompositeId? = null,

    @Column(nullable = false)
    @field:NotNull
    @NotBlank
    var code: String? = null
) : BazeEntity(id) {
    constructor(
        workingSetId: UUID? = null,
        code: String? = null,
    ) : this(MyCompositeId(workingSetId, randomUUID()), code)
}

@Repository
@Transactional
interface DepartmentRepository : JpaRepository<Department, MyCompositeId>

@Repository
@Transactional
interface EmployeeRepository : JpaRepository<Employee, MyCompositeId>

@Repository
@Transactional
interface ManagerRepository : JpaRepository<Manager, MyCompositeId>

```

### Thanks!

Your help is greatly appreciated.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [July 15, 2024, 4:03pm UTC](https://discourse.hibernate.org/t/sharing-column-across-composite-primary-and-foreign-keys/9935/2 "2024-07-15T16:03:11Z")

</div>

If you want to model this, you will have to have a separate attribute mapping for the foreign key columns, just like you did in the mapping you posted.

> **Q1** : Can you help me understand what is happening behind the scenes?

Hibernate ORM needs all columns of an association to be either insertable/updatable or not.

> The only way I found of resolving this was to create a field `managerEntityId` which is insertable/updatable and a field `manager` that is not.

Yes, that’s the way to go for now.

> **Q2** : If I edit the class field so that the setter explicitly updates the `managerEntityId` field, then the test passes. Is there a way configure the entities such that Hibernate takes care of this?

No, there is not. You can try to use a `@PrePersist` entity listener for that if you want though.

> **Q3** : Can you show how I could achieve the same on a `@ManyToMany` field? As in, how can I, in the many-to-many table, re-use the same `working_set_id` field for both sides (e.g. SQL below)?

That is simply not possible right now, but I would suggest you try to create a reproducer with our [test case template](https://github.com/hibernate/hibernate-test-case-templates/blob/master/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java) and create a bug ticket in our [issue tracker](https://hibernate.atlassian.net) and attach that reproducer.

---

<div class="post-metadata">

**Author:** ![tamzord](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/tamzord/32/1912_2.png) [@tamzord](https://discourse.hibernate.org/u/tamzord)\
**Post date:** [July 16, 2024, 6:36am UTC](https://discourse.hibernate.org/t/sharing-column-across-composite-primary-and-foreign-keys/9935/3 "2024-07-16T06:36:14Z")

</div>

Thanks for the clarifications, @beikov.

I created a bug with a reproducer as you suggested:  
[https://hibernate.atlassian.net/browse/HHH-18388](https://hibernate.atlassian.net/browse/HHH-18388)

I hope the reproducer is ok: I am not used to using Hibernate without Spring.  
I am available for clarifications, if need be.
