0

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.

2
  • 1
    You can always upgrade to Postgres (SCNR) Aug 1 at 18:02
  • Upgrade to Postgres is not feasible for me and have to use the existing DB which is My SQL. Any other approach/alternative solution please?
    – user258442
    Aug 1 at 18:04

1 Answer 1

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 ....

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy