Multiple unique keys reported as "redundant indexes" by skeema lint

[jearle-dealnews]

Having the following example table:

CREATE TABLE `sample_table` (
    `sample_table_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `reference_table_one_id` bigint(20) unsigned NOT NULL,
    `reference_table_two_id` bigint(20) unsigned NOT NULL,
    `reference_table_three_id` bigint(20) unsigned NOT NULL,
    `reference_table_four_id` bigint(20) unsigned NOT NULL,
    PRIMARY KEY (`sample_table_id`),
    UNIQUE KEY `reference_table_one_id` (`reference_table_one_id`,`reference_table_two_id`,`reference_table_three_id`,`reference_table_four_id`),
    UNIQUE KEY `reference_table_one_id_2` (`reference_table_one_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

When I run skeema lint, I receive the following message:

Index reference_table_one_id_2 of table sample_table is redundant to larger index reference_table_one_id.

The intended purpose of these two unique keys is to constrain those columns to have unique values and therefore are not really redundant.

Is it possible for skeema to know the difference in index uses when doing this check? I am aware of the setting lint-dupe-index and that I can use it to ignore these messages, but lint-dupe-index is a useful check and has caught some redundant indexes that we have ended up dropping. I would prefer to not set it to ignore.

[evanelias]

Thank you for the issue report! You're absolutely correct, unique indexes should not be considered redundant in this situation. I'll commit a fix soon.

[jearle-dealnews]

Thank you

[evanelias]

A fix has been committed to the main branch here for the Skeema CLI, and will be included in the next binary release, likely in ~early January. This fix has also been deployed to Skeema Cloud Linter today. Thanks again for the bug report!

[evanelias]

This fix has been included in Skeema CLI v1.4.7, released today.