0

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 {...}
3
  • If this can't be done with constraint, I would like to make this work anyhow. I think that constraint would be the best, because it's simple, directly on database level; and save() operation on repository would produce exception. Are there any alternatives with transactions? Does searching for active relations in @Transactional method lock the rows so it cant be changed concurrently? Commented Jun 9, 2020 at 10:20
  • 1
    How about creating simply a unique key on dogId and disabledOn? 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. Commented Jun 10, 2020 at 7:03
  • Ah yes, that's reasonable. I was overthinking it. Thanks very much :) If you post this as the answer, I will accept it. Commented Jun 10, 2020 at 7:47

1 Answer 1

1

You can simply create a unique constraint for dogId and disabledOn.

It does add the limitation that no two relationships may end at the same time but this seems to fit your use case.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.