using spring data, I created User 1:N UserDog N:1 Dog relation. Both 1:N relations are unidirectional @ManyToOne with UserDog being the custom relation table.
User entity:
@Entity
public class User {
@Id
@GeneratedValue
private long id;
@Column(nullable = false)
private String name;
}
Dog entity:
@Entity
public class Dog {
@Id
@GeneratedValue
private long id;
@Column(nullable = false)
private String name;
}
User dog relation table:
@Entity
public class UserDog {
@Id
@GeneratedValue
private long id;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn
@OnDelete(action = OnDeleteAction.CASCADE)
private User user;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn
@OnDelete(action = OnDeleteAction.CASCADE)
private Dog dog;
@Column(nullable = false)
private Instant createdOn = Instant.now();
@Column
private Instant disabledOn;
}
Use case
Use case is to store history of User-Dog bindings, where the concrete Dog can be bound only to one User at the time.
That's why I added createdOn and disabledOn columns to UserDog. disabledOn being null indicates that the relation is active and the Dog can't be assigned another User. If disabledOn is not null, then the record is stored only for evidence purposes and the Dog can be assigned to the same or another User again.
Question
How to ensure that the combination of Dog's id and disabledOn being null is unique in UserDog table?
In pseudo code I want something like this:
@Entity
@UniqueConstraint({@UniqueConstraint(this.dog.id), @NullConstraint(this.disabledOn)})
public class UserDog {...}
dogIdanddisabledOn? It does the additional limitation that no two relationships may and at the same time but it seems that fits your usecase actually really nice.