Alternative/Substitute to Partial index in MySQL?

Question

MySQL does not allow partial index creation. How can you achieve partial indexing in My SQL without using triggers. For example:

TABLE: STUDENT

NAME	STATUS
XYZ	FAIL
XYZ	FAIL
XYZ	PASS
ABC	PASS
PQR	FAIL
PQR	FAIL
PQR	PASS

How to add a constraint in the above table to allow only 1 pass for a student but may have multiple fail.

This can easily be achieved using Partial index but how to achieve it without partial index in MySQL and without the use of triggers. Is there any other check/constraint which can be applied to solve the above use case.

Answer 1

CREATE TABLE TestResults (
    student_id ...,
    passed TINYINT NULL  COMMENT "NULL for FAIL, 1 for PASS",
    ...,
    PRIMARY KEY(...),
    UNIQUE(student_id, date_passed)
) ENGINE=InnoDB;

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, ...)
    VALUES
    (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 ....