Зачем удалять foreign key для того, чтобы удалить уникальный индекс?

Question

[Иван Торгов]

Пытаюсь разобраться в вопросе, но нахожу только решения вопроса, а не объяснение почему так.

Имеется 2 таблицы:

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `number` int(10) unsigned NOT NULL,
  `title` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id_number_unique` (`user_id`,`number`),
  CONSTRAINT `items_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

Она заполняется данными, всё хорошо.
Вдруг я осознал, что мне не нужна колонка number в таблице items.
Чтобы её удалить, я сначала должен удалить свой составной уникальный индекс.
Но почему-то MySQL не даёт мне это сделать просто так, без танцев с бубном.

Ввожу запрос:

DROP INDEX user_id_number_unique ON items;

На что получаю ошибку Cannot drop index 'user_id_number_unique': needed in a foreign key constraint.
И мне приходится сначала удалять внешний ключ (который у меня items_ibfk_1 из примера выше), удалять индекс user_id_number_unique, удалять поле number и снова возвращать внешний ключ.

Я не могу понять, чем мешает внешний ключ?

Answer 1

[Алексей Уколов]

Для внешнего ключа обязательно наличие индекса, иначе его не создать. Если индекса нет, при создании внешнего ключа он создаётся автоматически, поэтому может казаться, что связи между ними нет.

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

https://dev.mysql.com/doc/refman/5.7/en/create-tab...

Comments

[Иван Торгов]

Ах вон оно что. Особенно вот это всё проясняет: "This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint".

Получается, что если создать таблицу вот так, то всё будет ОК при удалении уникального индекса:

CREATE TABLE `items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `number` int(10) unsigned NOT NULL,
  `title` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id_number_unique` (`user_id`,`number`),
  KEY `user_id_foreign` (`user_id`),
  CONSTRAINT `items_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

Спасибо!

[Boris Köln]

Алексей Уколов , надо уточнить, что это только для MySQL.
А, например, PostgreSQL не требует индекса и не создает его автоматически.

[Алексей Уколов]

Boris Korobkov, это уже в тегах вопроса уточнено. И в тексте вопроса. И в тексте ответа. И в ссылке на документацию. Думаю, все уже догадались :)

[Виктор]

Ищу информацию по этому:

This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint

На одном сервере исходный индекс удаляется, на другом нет. Хотя версия MySQL в обоих случаях 5.7 (но на одном из них Percona).

Не могу понять, от чего зависит, будет ли удаляться исходный индекс. Может какая-то настройка есть или это всегда остается на усмотрение MySQL. Но тогда получается, что на разных серверах может быть разный состав индексов.