MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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`),
    UNIQUE(`user_id`, `test_id`, (
        CASE WHEN `completed_date` IS NOT NULL
        THEN NULL
        ELSE 0
    END))
);
insert into executed_tests (user_id, test_id, start_date) values (1, 2, '1981-08-24');
select * from executed_tests;


id
user_id
test_id
start_date
completed_date


1
1
2
1981-08-24
null


insert into executed_tests (user_id, test_id, start_date) values (1, 2, '1981-08-24');

Duplicate entry '1-2-0' for key 'executed_tests.functional_index'


select * from executed_tests;


id
user_id
test_id
start_date
completed_date


1
1
2
1981-08-24
null


insert into executed_tests (user_id, test_id, start_date, completed_date) values (1, 2, '1981-08-24', '1981-08-24');
select * from executed_tests;


id
user_id
test_id
start_date
completed_date


1
1
2
1981-08-24
null

3
1
2
1981-08-24
1981-08-24


insert into executed_tests (user_id, test_id, start_date, completed_date) values (1, 2, '1981-08-24', '1981-08-24');
select * from executed_tests;


id
user_id
test_id
start_date
completed_date


1
1
2
1981-08-24
null

3
1
2
1981-08-24
1981-08-24

4
1
2
1981-08-24
1981-08-24

id	user_id	test_id	start_date	completed_date
1	1	2	1981-08-24	null
3	1	2	1981-08-24	1981-08-24
4	1	2	1981-08-24	1981-08-24