CREATE TABLE TestResults (
passed TINYINT NULL COMMENT "NULL for FAIL, 1 for PASS",
This takes advantage of
UNIQUE allowing NULLs, but not treating them as duplicates. That is, do something like this:
INSERT INTO TestResults
(student_id, passed, ...)
(123, NULL, ...);
That pattern will allow
(123, NULL, ...) -- OK
(123, NULL, ...) -- OK (though seemingly a duplicate)
(123, 1, ...) -- OK (first pass)
(123, 1, ...) -- Error -- UNIQUEness constraint
If you don't catch the error, it will simply fail to insert another row; is that what you want? If so, then use
INSERT IGNORE so there won't even be an 'error'.
You may want another column
date_passed DATETIME NULL to say when the [first] pass occurred.
You probably want to know if student 123 has passed all the rows for 123 need to be checked. Let's use MAX to aggregate it down to a simple result:
SELECT MAX(passed) FROM TestResults WHERE student_id=123;
will return NULL (no pass) or 1 (pass). Consider this for turning it into a human-friendly string:
IF(MAX(passed), 'Passed', 'Failed')
If you don't really need a list of all the FAILs, why not have simply a single row for the current 'status' of each student? Then use
UPDATE, or perhaps
INSERT ... ON DUPLICATE KEY UPDATE ....