Report unique indices that cover nullable columns

[macbre]

https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-unique

A UNIQUE index permits multiple NULL values for columns that can contain NULL.

Links

• https://medium.com/@aleksandrasays/dealing-with-mysql-nulls-and-unique-constraint-d260f6b40e60

Examples

CREATE TABLE `progress` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `plan_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `question_id` int(10) unsigned NOT NULL,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_plan_progress_unique` (`user_id`,`question_id`,`deleted_at`),
  KEY `users_plan_progress_plan_id_index` (`plan_id`)
) ENGINE=InnoDB;

Fixed

CREATE TABLE `progress` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `plan_id` int(10) unsigned NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `question_id` int(10) unsigned NOT NULL,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `is_not_archived` tinyint(4) GENERATED ALWAYS AS (if((`deleted_at` is null),1,NULL)) VIRTUAL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_plan_progress_unique` (`user_id`,`question_id`,`is_not_archived`),
  KEY `users_plan_progress_plan_id_index` (`plan_id`)
) ENGINE=InnoDB;