Обязательный ли индекс для foreign key?

Question

[Webber]

В доке mysql индекс на fk обязательный и создается авто, это указано в доке:

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.

В postgres, sqlite проверил индекс авто не создается.

Это особенность mysql или "правило хорошего тона" во всех бд создавать индекс?

Answer 1

[Anton_habr_2020]

В MySQL индекс создает тот же MySQLWorkbench при создании ForeingKey, однако вы можете удалить его вручную или создать FK скриптом, не создавая индекс. Так что индекс не обязателен.

Однако, при изменении связанных записей БД попробует применить настройки FK для вашей таблицы и изменить связанные записи или обнулить их. А при удалении проверить можно ли удалять.
Если данных в таблице много, то без индекса эти операции займут много времени.

Comments

[Webber]

В Workbench индекс создается, но при удалении индекса удаляется и связь. При удалении индекса mysql не даст вам это сделать и выкинет ошибку Зачем удалять foreign key для того, чтобы удалить уникальный индекс?

[Anton_habr_2020]

Webber, Да ошибся. Действительно автоматом удаляет.
Получается нельзя FK без индекса создать, вот и все.
А зачем - я вроде ответил, для скорости проверки целостности.

[Webber]

Anton_habr_2020, дак про mysql я делали знаю. Вопрос же был почему в других бд другое поведение и нужно ли в других создавать по хорошему тоже

Answer 2

[galaxy]

Postgres:

Внешний ключ должен ссылаться на столбцы, образующие первичный ключ или ограничение уникальности. Таким образом, для связанных столбцов всегда будет существовать индекс (определённый соответствующим первичным ключом или ограничением), а значит проверки соответствия связанной строки будут выполняться эффективно

Нужен UNIQUE CONSTRAINT на родительской таблице, иначе как понять, вообще говоря, на какую именно запись ссылается внешний ключ на дочерней?
Де-факто ограничения уникальности везде реализуются через индексы.

Comments

[Webber]

на родительской понятно. там или на primary или unique. Речь про foreign таблицу

[galaxy]

Webber, необязательно. Если у вас нет запросов, которые подразумевают автоматическую проверку foreign key, можете не создавать