title: SQl error - Referential integrity constraint violation when persisting several entities at once
body: <h3>Problem I'm trying to solve</h3>
  <p>I'm trying to model a <code>@ManyToMany</code> relation between a <code>User</code> and <code>Role</code>, such that a user can have <em>n</em> roles, and one role is referenced by several users. A role can be persisted even if it's not referenced by any user (detached), and a user with no roles is allowed too.</p>
  <p>The same kind of relation must be built between <code>Role</code> and <code>ResourcePermission</code>.</p>
  <p>To give you an idea about how each entity looks like:</p>
  <ul>
  <li>Both <code>ResourcePermission</code> and <code>Role</code> have a finite set of values. For example, if <code>Patient</code> happens to be a resource, then one resource permission could be <code>&quot;PATIENT:READ&quot;</code> or <code>&quot;PATIENT:WRITE&quot;</code>, and the role <code>DOCTOR</code> has several of these permissions. I hope it's clear sofar how my data model looks like.</li>
  </ul>
  <h3>What I'm using</h3>
  <ul>
  <li>Currently, I'm using <code>spring-data-jpa</code> version <code>2.4.2</code> to model my entities, and to create my CRUD repos. Except for base path and media type, I don't have any specific configuration (all is set to default).</li>
  <li>Hibernate is my persistence provider atm .</li>
  <li>Concerning my datasource, I'm using in-memory H2 for my development environment, and again no specific configuration there either.</li>
  </ul>
  <h3>How I'm solving it</h3>
  <p>Here's how my <strong>entities</strong> look like</p>
  <p><strong>User.java</strong></p>
  <pre class="lang-java prettyprint-override"><code>@Table
  @Entity
  @Data
  public class User implements Serializable {
  
      private static final long serialVersionUID = 1123146940559321847L;
  
      @Id
      @GeneratedValue(generator = &quot;user-id-generator&quot;)
      @GenericGenerator(name = &quot;user-id-generator&quot;,
              strategy = &quot;....security.entity.UserIdGenerator&quot;,
              parameters = @Parameter(name = &quot;prefix&quot;, value = &quot;USER-&quot;)
      )
      @Column(unique = true, nullable = false)
      private String id;
  
  
      @Column
      private int age;
      @Column(unique = true, nullable = false)
      private String username;
      @Column(unique = false, nullable = false)
      private String password;
  
      @ManyToMany(
              fetch = FetchType.LAZY,
              cascade = CascadeType.MERGE
      )
      @JoinTable(
              name = &quot;user_role&quot;,
              joinColumns = @JoinColumn(name = &quot;user_id&quot;),
              inverseJoinColumns = @JoinColumn(name = &quot;role_id&quot;)
      )
      private List&lt;Role&gt; roles = Collections.emptyList();
  
      public User withId(final String id) {
          this.id = id;
          return this;
      }
  
      public User withAge(final int age) {
          this.age = age;
          return this;
      }
  
      public User withUsername(final String username) {
          this.username = username;
          return this;
      }
  
      public User withPassword(final String password) {
          this.password = password;
          return this;
      }
  
      public User withRoles(final Role... roles) {
          return withRoles(Arrays.stream(roles).collect(Collectors.toList()));
      }
  
      public User withRoles(final List&lt;Role&gt; roles) {
          this.roles = roles;
          return this;
      }
  }
  </code></pre>
  <p><strong>Role.java</strong></p>
  <pre class="lang-java prettyprint-override"><code>@Data
  @NoArgsConstructor
  @Table
  @Entity
  public class Role implements Serializable {
  
      private static final long serialVersionUID = 812344454009121807L;
  
      @Id
      private String roleName;
  
      @ManyToMany(
              fetch = FetchType.LAZY,
              cascade = { CascadeType.MERGE, CascadeType.PERSIST, CascadeType.DETACH }
      )
      @JoinTable(
              name = &quot;role_resource_permission&quot;,
              joinColumns = @JoinColumn(name = &quot;role_id&quot;),
              inverseJoinColumns = @JoinColumn(name = &quot;resource_permission_id&quot;)
      )
      private Set&lt;ResourcePermission&gt; resourcePermissions = Collections.emptySet();
  
      @ManyToMany(
              mappedBy = &quot;roles&quot;,
              fetch = FetchType.LAZY,
              cascade = { CascadeType.MERGE, CascadeType.PERSIST, CascadeType.DETACH }
      )
      private List&lt;User&gt; users = Collections.emptyList();
  
  
      public Role(final String roleName) {
          setRoleName(roleName);
      }
  
      public void setRoleName(final String roleName) {
          final RoleType roleType = RoleType.of(roleName);
          this.roleName = roleType.getRoleName();
          final Set&lt;ResourcePermission&gt; resourcePermissions = roleType.getResourcePermissions().stream()
                  .map(ResourcePermissionType::getPermissionName)
                  .map(ResourcePermission::new)
                  .collect(Collectors.toSet());
          setResourcePermissions(resourcePermissions);
      }
  
      public void setResourcePermissions(final Set&lt;ResourcePermission&gt; resourcePermissions) {
          if (this.resourcePermissions.isEmpty()) {
              this.resourcePermissions = resourcePermissions;
          }
      }
  
  }
  </code></pre>
  <p><strong>ResourcePermission.java</strong></p>
  <pre class="lang-java prettyprint-override"><code>@NoArgsConstructor
  @Data
  @Table
  @Entity
  public class ResourcePermission implements Serializable {
  
      private static final long serialVersionUID = 883231454000721867L;
  
      @Id
      private String permissionName;
  
      public ResourcePermission(final String permissionName) {
          setPermissionName(permissionName);
      }
  
      @ManyToMany(
              mappedBy = &quot;resourcePermissions&quot;,
              fetch = FetchType.LAZY,
              cascade = { CascadeType.MERGE, CascadeType.PERSIST, CascadeType.DETACH }
      )
      private Set&lt;Role&gt; roles = Collections.emptySet();
  
      public void setPermissionName(String permissionName) {
          final ResourcePermissionType permissionType = ResourcePermissionType.of(permissionName);
          this.permissionName = permissionType.getPermissionName();
      }
  }
  </code></pre>
  <p><strong>RoleType.java</strong></p>
  <pre class="lang-java prettyprint-override"><code>@AllArgsConstructor(access = AccessLevel.PRIVATE)
  public enum RoleType {
  
      DOCTOR(&quot;DOCTOR&quot;, doctorsPermissions()),
      TECHNICIAN(&quot;TECHNICIAN&quot;, technicianPermission()),
      ADMIN(&quot;ADMIN&quot;, adminPermissions());
  
      @Getter
      private String roleName;
      @Getter
      private final List&lt;ResourcePermissionType&gt; resourcePermissions;
  
       public static RoleType of(final String roleName) {
          return Arrays.stream(values())
                  .filter(roleType -&gt; roleType.getRoleName().equals(roleName.toUpperCase()))
                  .findFirst()
                  .orElseThrow(IllegalArgumentException::new);
       }
  
      private static List&lt;ResourcePermissionType&gt; doctorsPermissions() {
          return Arrays.asList(
                  ENCOUNTER_READ, ENCOUNTER_WRITE,
                  PATIENT_READ, PATIENT_WRITE
          );
      }
  
      private static List&lt;ResourcePermissionType&gt; adminPermissions() {
          return Arrays.asList(
                  ENCOUNTER_READ, ENCOUNTER_WRITE,
                  BUILDING_UNIT_READ, BUILDING_UNIT_WRITE,
                  ORG_UNIT_READ, ORG_UNIT_WRITE
          );
      }
  
      private static List&lt;ResourcePermissionType&gt; technicianPermission() {
          return Arrays.asList(
                  ENCOUNTER_READ, ENCOUNTER_WRITE,
                  BUILDING_UNIT_READ, BUILDING_UNIT_WRITE
          );
      }
  
  }
  </code></pre>
  <p><strong>ResourcePermissoinType.java</strong></p>
  <pre class="lang-java prettyprint-override"><code>@AllArgsConstructor(access = AccessLevel.PRIVATE)
  public enum ResourcePermissionType implements Serializable {
  
      PATIENT_READ(&quot;PATIENT:READ&quot;), PATIENT_WRITE(&quot;PATIENT:WRITE&quot;),
      ENCOUNTER_READ(&quot;ENCOUNTER:READ&quot;), ENCOUNTER_WRITE(&quot;ENCOUNTER:WRITE&quot;),
      BUILDING_UNIT_READ(&quot;BUILDING_UNIT:READ&quot;), BUILDING_UNIT_WRITE(&quot;BUILDING_UNIT:WRITE&quot;),
      ORG_UNIT_READ(&quot;ORG_UNIT:READ&quot;), ORG_UNIT_WRITE(&quot;ORG_UNIT:WRITE&quot;);
  
      @Getter
      private String permissionName;
  
      public static ResourcePermissionType of(final String permissionName) {
          return Arrays.stream(values())
                  .filter(v -&gt; v.getPermissionName().equals((permissionName.toUpperCase())))
                  .findFirst()
                  .orElseThrow(IllegalArgumentException::new);
      }
  
  }
  
  </code></pre>
  <p>Unfortunately, the javax persistence API does not accept enums as entities. I tried using <code>@Embeddable</code> and <code>@IdClass</code> too, but that didn't work out for me either. I was not able to generate the schema that I had in mind. On the other hand, the schema was successfully generated using this model.</p>
  <p>At the moment, both the Role repository as well as the Resource Permission repository are not exported (<code>@RepositoryRestResource(..., exported = false)</code>), so in order for you to persist those two entities, you'd have to provide that data in <code>User</code>. Keep that in mind, because that's also a part of the discussion that I want to talk about.</p>
  <p>Now let's examine this integration test for the <code>UserCrudRepository</code> that will attempt to add a new user after a successful authentication.</p>
  <pre class="lang-java prettyprint-override"><code>@TestMethodOrder(OrderAnnotation.class)
  @SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
  @AutoConfigureMockMvc
  class UserCrudRepositoryApiITest {
  
  private final List&lt;User&gt; testUsers = Arrays.asList(
                  new User().withUsername(&quot;dummy_username_01&quot;).withPassword(&quot;dummy_password_01&quot;).withAge(35)
                          .withRoles(new Role(&quot;ADMIN&quot;)),
                  new User().withUsername(&quot;dummy_username_02&quot;).withPassword(&quot;dummy_password_02&quot;).withAge(40)
                          .withRoles(new Role(&quot;DOCTOR&quot;)),
                  new User().withUsername(&quot;dummy_username_03&quot;).withPassword(&quot;dummy_password_03&quot;).withAge(45)
          );
  .
  .
      @Order(1)
      @Test
      public void afterAuthenticationAddNewUser() throws Exception {
          final String generatedToken = login();
          // serialize the user
          final String requestJson = objectMapper.writeValueAsString(testUsers.get(0));
          final RequestBuilder request = MockMvcRequestBuilders.post(USER_CRUD_BASE_URL)
                  .header(HttpHeaders.AUTHORIZATION, generatedToken)
                  .contentType(MediaType.APPLICATION_JSON)
                  .content(requestJson);
          final String serializedContent = mvc.perform(request)
                  .andExpect(status().isCreated())
                  .andReturn()
                  .getResponse()
                  .getContentAsString();
          final User storedUser = objectMapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false)
                  .readValue(serializedContent, User.class);
  
          assertThat(storedUser).isEqualTo(testUsers.get(0));
      }
  .
  .
  }
  </code></pre>
  <p>In here, I'm getting a status code conflict 409, and not able to persist all entities at once.</p>
  <p>Unfortunately, SO allows only 30000 character, so please navigate to <a href="https://github.com/kareemjeiroudi/referential-integrity-constraint-violation" rel="nofollow noreferrer">this repo</a> if you would like to take a look at the log.</p>
  <h3>My Questions</h3>
  <ol>
  <li>I couldn't for the life of me understand where that referential integrity constraint violation
  is occurring. Any idea?</li>
  <li>Any suggestions on how to model these relations in a better way are welcome!</li>
  <li>Another problem I'm having with JPA repos is that the only way to persist roles and resource permissions is by providing that data in the user's body. I would like those entities to be managed independently of the user (each with its own separate repository), so I tried exporting their repositories. However, the problem then is that you no longer can pass <code>Role</code> data in the body of a <code>User</code>, but rather A reference to that entity. Is there a way to get the best of both worlds.</li>
  </ol>
  <p>I hope I made my problem clear, if not, I'd be happy to elaborate more.</p>