1

I created a table that had a unique constraint of user_id , document_id and status:

@Entity
@Table(uniqueConstraints = @UniqueConstraint(columnNames = { "user_id", "document_id",
        "status" }), name = "acquired_document")
@EntityListeners(AuditingEntityListener.class)
public class AcquiredDocument {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Long acquiredDocumentId;

    @ManyToOne
    @JoinColumn(name = "user_id", nullable = false, updatable = false)
    private User user;

    @Column(name = "document_id", nullable = false, updatable = false)
    private String documentId;

    @Column(name = "status", nullable = false)
    private String status;

Now due to change in requirements we need to have a unique constraint disable on these three columns if the status is "FAILED" (to enable multiple failed documents to be acquired).

So this is invalid:

+-----+----------+-------------+--------+
| id  |  user_id | document_id | status |
+-----+----------+-------------+--------+
|  1  | 1        |     1       | PAID   |
|  2  | 1        |     1       | PAID   |
+-----+----------+-------------+--------+

But this should not be:

+-----+----------+-------------+--------+
| id  |  user_id | document_id | status | 
+-----+----------+-------------+--------+
|  1  | 1        |     1       | FAILED |
|  2  | 1        |     1       | FAILED |
+-----+----------+-------------+--------+

I am using JPA (JAVA 1.8) and mySQL server 5.7.

ab AL
  • 31
  • 1
  • 9
  • I'm not sure MySQL supports a "partial" unique constraint, i.e. one that only applies if status is not FAILED. If this is supported you might be able to use that. Alternatively, you could try to add a 5th column like "attempt" which is part of the constraint and must be null or have a defined value for anything but FAILED (you could use a check constraint for this). – Thomas Nov 30 '21 at 10:53
  • mysql does not support exemptions from a unique index. Either it applies to all records on the table or does not apply at all. – Shadow Nov 30 '21 at 11:44
  • @Thomas can you elaborate on the 5th column alternative please? – ab AL Nov 30 '21 at 12:58
  • Well, as an example you could add another column like "attempt" which is basially a sequence number of each failed operation, i.e. you set a new value for each row with a FAILED status and set it to NULL or some default value if the status is something else. Then add "attempt" to your unique constraint which results in multiple failed attempts being supported (because the key differs by "attempt") but allows for only one record with another status (because "attempt" will have the same value for those). ... – Thomas Nov 30 '21 at 13:10
  • ... To make it safe you need to ensure that "attempt" has the default value or NULL if the status is not FAILED. That's where a CHECK constraint might come in handy. It doesn't seem to be supported by MySQL 5.7 but there seems to be a workaround as described here: https://dasini.net/blog/2019/05/14/check-constraints-in-mysql/ – Thomas Nov 30 '21 at 13:13
  • Related: https://stackoverflow.com/questions/4955704/unique-constraint-only-when-a-field-contains-a-specific-value – A.Casanova Jun 06 '23 at 08:34

0 Answers0