hibernate two column unique index cannot update values

Question

So I am trying to add a unique constraint that uses two column. I added the unique constraint in db and I have following Java code

@Entity
@Table(
    name = "test_table",
    uniqueConstraints = @UniqueConstraint(
        columnNames = {
            "other_table_id",
            "sort_order"
        },
        name = "my_unique_constraint")
)
class SomeTable {

    private String label;

    @ManyToOne(optional = false)
    @JoinColumn(name = "other_table_id", nullable = false)
    private OtherTable otherTable;

    @NotNull
    @Column(name = "sort_order", nullable = false)
    private int sortOrder;
}

When I modify the sorting for this table and update the record I get

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
 Duplicate entry '1-2' for key 'my_unique_constraint'

How do I implement @UniqueConstraint such that I don't get this kind of error.

Answer 1

You get this error because your uniqueConstraints aren't separated.

So my approach is to separate the two constraints:

@Table(
    name = "test_table",
    uniqueConstraints = {
            @UniqueConstraint(columnNames = "other_table_id"),
            @UniqueConstraint(columnNames = "sort_order")
    },
        name = "my_unique_constraint")
)