Add unique constraint for validated submission per student per exam
We have wanted to add a unique constraint for (submission.student_id, submission.exam_id)
for some time, but only for validated submissions (thus with submission.validated = TRUE
). Adding such a constraint on the MySQL is not possible naturally, but I just realized we can exploit the fact that MySQL ignores NULL
values for the unique constraint. See the MySQL docs under 'Unique Indexes'.
For this, we will store unvalidated submissions with submission.validated = NULL
, and validated submissions with submission.validated = TRUE
. After that it's as simple as adding a unique constraint on (submission.student_id, submission.exam_id, submission.validated)
.