integrity constraint violation: unique constraint or index violation on Foreign key HSQL

Question

I'm testing the underlying model of a HSQL database using Hibernate/Spring Boot and I've run into an issue I cannot find a solution to.

This is my simple test, I'm trying to create a shoebox entity and save it to the database with a User object set as the FK for Owner:

@TestConfiguration
static class ShoeboxServiceTestContextConfiguration {

    @Bean
    public ShoeboxService shoeboxService() {
        return new ShoeboxService();
    }

    @Bean
    public UserService userService() {
        return new UserService();
    }
}

@Autowired
UserService users;

@Autowired
ShoeboxService shoeboxes;

@Test
public void testSave()
{
    System.out.println("save");
    int userId = 1;
    User user = new User(userId, "Foo", "[email protected]");

    user = users.save(user);

    Shoebox sb = new Shoebox(user, "Name", "Context", "Comment", false);
    UUID sbId = shoeboxes.save(sb).getId();

    sb = shoeboxes.findOne(sbId);
    assertNotNull(sb);
    assertEquals(sb.getName(), "Name");
    assertEquals(sb.getContext(), "Context");
    assertEquals(sb.getComment(), "Comment");
    assertEquals(sb.isShare(), false);

    shoeboxes.deleteById(sbId);
    users.deleteById(userId);
}

However when it gets it throws a

integrity constraint violation: unique constraint or index violation; SYS_PK_10126 table: USER

exception when it tries to save the Shoebox to the DB. It successfully persist the User, and it succeeds in persisting the Shoebox object when there is no Owner FK attached to it, but crashes when the FK is supplied.

Here is my User POJO:

@Entity
@Table(name="User")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class User implements Serializable
{

    @Id
    @Column(name = "ID")
    private long ID;

    @Column(name = "Name")
    private String name;

    @Column(name = "Email")
    private String email;

    @OneToOne(fetch = FetchType.LAZY)
    private Shoebox currentlySelectedBox;

    @OneToMany(fetch = FetchType.LAZY)
    @JsonManagedReference(value="shoebox_owner")
    private List<Shoebox> shoeboxes;

    // Contructors, Getters/Setters etc.
}

And my Shoebox POJO:

@Entity
@Table(name="Shoebox")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class Shoebox implements Serializable
{
    @Id
    @Column(name="ID")
    UUID ID;

    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name="OwnerID")
    @JsonBackReference(value="shoebox_owner")
    User owner;

    @Column(name="Name")
    String name;

    @Column(name="Context")
    String context;

    @Column(name="Comment")
    String comment;

    @Column(name="Shared")
    boolean share;

    @Column(name="CreationDate")
    LocalDateTime creationDate;

    // Contructors, Getters/Setters etc.
}

Here is the HSQL creation script for the DB:

CREATE MEMORY TABLE PUBLIC.SHOEBOX(ID BINARY(255) NOT NULL PRIMARY KEY,COMMENT VARCHAR(255),CONTEXT VARCHAR(255),CREATIONDATE TIMESTAMP,NAME VARCHAR(255),SHARED BOOLEAN,OWNERID BIGINT)
CREATE MEMORY TABLE PUBLIC.USER(ID BIGINT NOT NULL PRIMARY KEY,EMAIL VARCHAR(255),NAME VARCHAR(255),CURRENTLYSELECTEDBOX_ID BINARY(255),CONSTRAINT FK3T924ODM2BIK5543K0E3UEGP FOREIGN KEY(CURRENTLYSELECTEDBOX_ID) REFERENCES PUBLIC.SHOEBOX(ID))
CREATE MEMORY TABLE PUBLIC.USER_SHOEBOX(USER_ID BIGINT NOT NULL,SHOEBOXES_ID BINARY(255) NOT NULL,CONSTRAINT FK5W8WMFC5E9RMEK7VC4N76MQVQ FOREIGN KEY(SHOEBOXES_ID) REFERENCES PUBLIC.SHOEBOX(ID),CONSTRAINT FKIR9SOKRCOQ33LCQTNR0LDXO93 FOREIGN KEY(USER_ID) REFERENCES PUBLIC.SHOEBOXUSER(ID),CONSTRAINT UK_508XA86IDIHP04FQD3D6GF8D7 UNIQUE(SHOEBOXES_ID))
ALTER TABLE PUBLIC.SHOEBOX ADD CONSTRAINT FK3J9RQBYW5VQ0IRF3FWYPG7LAB FOREIGN KEY(OWNERID) REFERENCES PUBLIC.USER(ID)

Why is the exception being triggered? Is there something wrong with my annotations and PK/FK relationships between the objects?

Many Thanks.

Answer 1

The issue is

@ManyToOne(cascade = CascadeType.ALL)

With CascadeType.ALL, any operations will extend to the other entities. So in this case the save method is cascading on the shoebox's user attempting to save it again. Since you are using a static id of 1, it is causing a key constraint.