How to use Hibernate Check constraint in this case?

Question

I want to use check constraint to verify if there are more students in the subject more than vacancies. These are the entities:

SubjectOffer

@Entity
@SequenceGenerator(name = "SUBJECT_OFFER_SEQ", sequenceName = "SUBJECT_OFFER_SEQ")
@Table(name = "SUBJECT_OFFER", uniqueConstraints = {
        @UniqueConstraint(name = "UQ_SUBJECT_OFFER_COURSE_SUBJECT_SEMESTER_CLASS", columnNames = {"COURSE_ID", "SUBJECT_ID", "SEMESTER", "CLASS_NUMBER"})})
@Check(constraints = "COUNT(STUDENT_SUBJECT_ID) <= VACANCIES")
public class SubjectOffer {

    @Id
    @GeneratedValue(generator = "SUBJECT_OFFER_SEQ")
    @Column(name = "SUBJECT_OFFER_ID", nullable = false)
    private Long id;

    @OneToMany(fetch = FetchType.LAZY, orphanRemoval = true, cascade = CascadeType.ALL)
    @JoinColumn(name = "STUDENT_SUBJECT_ID")
    private Set<StudentSubject> studentSubjects = new HashSet<>();

    //other attributes

    @Column(name = "VACANCIES", nullable = false)
    private int vacancies;
}

StudentSubject

@Entity
@Table(name = "STUDENT_SUBJECT")
public class StudentSubject {

    @EmbeddedId
    private StudentSubjectId id = new StudentSubjectId();

    @MapsId("studentId")
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "STUDENT_ID", nullable = false)
    private Student student;

    @MapsId("subjectOfferId")
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "SUBJECT_OFFER_ID", nullable = false)
    private SubjectOffer subjectOffer;

    @Column(name = "SEMESTER", nullable = false)
    private int semester;

    @Column(name = "GRADE")
    private BigDecimal grade;

}

I also tried column definition in Set @JoinColumn but it didn't work

Answer 1

SQL check constraints only work on a single table. What you want is a so called SQL assertion constraint, but no database implements that. The best you can do is to pre-create rows for the amount of vacancies and just assign students to these rows without ever creating more rows. This way, you can make sure that you only assign as many students as there are vacancies, given that you use optimistic/pessimistic locking when assigning a student.