CODEWITHSUNDEEP
mysqlconstraintsunique-constraintmysql-5.6
Gus Costa
Gus Costa

Reputation: 619

Set a unique constraint only when a field is null

I have this table:

CREATE TABLE `executed_tests` (
    `id` INTEGER AUTO_INCREMENT NOT NULL,
    `user_id` INTEGER NOT NULL,
    `test_id` INTEGER NOT NULL,
    `start_date` DATE NOT NULL,
    `completed_date` DATE,
    PRIMARY KEY (`id`)
);

I want to set up an unique constraint on fields user_id and test_id, but only when conclusion_date is null. If conclusion_date is not null, the constraint doesn't apply.

So there will exist only one incomplete execution per user and test.

Something like this:

UNIQUE(`user_id`, `test_id`) WHEN (`completed_date` IS NULL)

How can I accomplish this on MySQL 5.6?

Upvotes: 0

Views: 379

Answers (1)

Marvin
Marvin

Reputation: 14255

MySQL supports functional key parts since 8.0.13.

  • If your version is sufficiently recent you can define your index as:

    UNIQUE(`user_id`, `test_id`, (IFNULL(`completed_date`, -1)))

    (Demo on dbfiddle.uk)

    Note that the above index will also prevent duplciate dates for completed executions. If those should be valid then a slightly modified index would work:

    UNIQUE(`user_id`, `test_id`, (
    CASE WHEN `completed_date` IS NOT NULL
    THEN NULL
    ELSE 0
END))

    (Demo on dbfiddle.uk)

    Although then it starts to feel a bit dirty ;)

  • If you have at least version 5.7 you can use a (virtual) generated column as workaround:

    CREATE TABLE `executed_tests` (
    `id` INTEGER AUTO_INCREMENT NOT NULL,
    `user_id` INTEGER NOT NULL,
    `test_id` INTEGER NOT NULL,
    `start_date` DATE NOT NULL,
    `completed_date` DATE,
    `_helper` CHAR(11) AS (IFNULL(`completed_date`, -1)),
    PRIMARY KEY (`id`),
    UNIQUE(`user_id`, `test_id`, `_helper`)
);

    (Demo on dbfiddle.uk)

  • If you are stuck on 5.6 then a combination of a regular (non-virtual) column and slightly modified INSERT statements would work:

    CREATE TABLE `executed_tests` (
    `id` INTEGER AUTO_INCREMENT NOT NULL,
    `user_id` INTEGER NOT NULL,
    `test_id` INTEGER NOT NULL,
    `start_date` DATE NOT NULL,
    `completed_date` DATE,
    `is_open` BOOLEAN,
    PRIMARY KEY (`id`),
    UNIQUE(`user_id`, `test_id`, `is_open`)
);

    In this case you would set is_open to true for incomplete executions and to NULL after completion, making use of the fact that two NULLs are treated as not equal.

    (Demo on dbfiddle.uk)

Upvotes: 1

Related Questions