How to Resolve Circular Foreign Key Constraints with NOT NULL in Hibernate

I’m dealing with a cyclic dependency issue between two entities (Store and Staff) in MySQL, which is causing issues when trying to save data using Hibernate ORM (version 6.6.0.Final, no Spring). The problem arises from foreign key constraints and NOT NULL restrictions in the database schema.

Database Schema (MySQL):

Here are the relevant sections from my database dump:

store table:

CREATE TABLE `store` (
  `store_id` tinyint unsigned NOT NULL AUTO_INCREMENT,
  `manager_staff_id` tinyint unsigned NOT NULL,
  `address_id` smallint unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`store_id`),
  UNIQUE KEY `idx_unique_manager` (`manager_staff_id`),
  KEY `idx_fk_address_id` (`address_id`),
  CONSTRAINT `fk_store_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_store_staff` FOREIGN KEY (`manager_staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

staff table:

CREATE TABLE `staff` (
  `staff_id` tinyint unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `address_id` smallint unsigned NOT NULL,
  `store_id` tinyint unsigned NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `username` varchar(16) NOT NULL,
  `password` varchar(40),
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`staff_id`),
  KEY `idx_fk_store_id` (`store_id`),
  KEY `idx_fk_address_id` (`address_id`),
  CONSTRAINT `fk_staff_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_staff_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Entity Classes (Java):

Here are the Java entity classes for Store and Staff:

Store.java:

@Entity
@Table(schema = "movie", name = "store")
public class Store {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Short id;

    @ManyToOne
    @JoinColumn(name = "manager_staff_id", nullable = false)
    private Staff mainStaff;

    @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    @JoinColumn(name = "address_id", nullable = false)
    private Address address;

    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "mainStore")
    private List<Staff> staffList;

    // other fields...
}

Staff.java:

@Entity
@Table(schema = "movie", name = "staff")
public class Staff {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Short id;

    @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    @JoinColumn(name = "address_id", nullable = false)
    private Address address;

    @ManyToOne
    @JoinColumn(name = "store_id", nullable = false)
    private Store mainStore;

    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "mainStaff")
    private List<Store> stores;

    // other fields...
}

Problem:

When attempting to persist either Store or Staff, I encounter the following exception:

java.lang.IllegalStateException: org.hibernate.TransientPropertyValueException: Not-null property references a transient value - transient instance must be saved before current operation:
Caused by: org.hibernate.TransientPropertyValueException: Not-null property references a transient value - transient instance must be saved before current operation:

It’s clear that the cyclic foreign key constraint (store.manager_staff_id referencing staff.staff_id, and staff.store_id referencing store.store_id) creates a dependency loop, which prevents me from saving either entity first. I have tried:

1. Removing cascading and saving entities manually (doesn’t work).
2. Attempting to manage transaction boundaries to ensure one is persisted before the other, but the circular dependency causes the same issue.

Question:

Is there a way to resolve this issue within the confines of using Hibernate (without Spring)? Can this be solved purely on the ORM level (Java side) without altering the database schema or constraints (since this is beyond my control)?

I’m looking for guidance on:

• Correctly configuring cascading, if applicable.
• Alternative ways to resolve the circular dependency while still adhering to the constraints imposed by the existing MySQL schema.

P.S.: I’m using Hibernate 6.6.0.Final and working without Spring.

You need to sign in to view this answers