MariaDB handling composite unique indexes that are foreign keys at the same time I have a possible issue with how MariaDB (and probably MySQL) handles composite unique indexes that are also foreign keys. Not sure if that is by design and if it is, why does it work like that.

I have a table called action_contact. It has a foreign key action_id to the 'action' table and contact_id to the 'contact' table. Both are unsigned bigint columns. I need these two fields to be unique indexes as well. If I don't add the unique index, both fields are also indexes in the database by default, which is fine. But, once I add the unique composite index (action_id, contact_id) - it’s added in the database, but the normal index for contact_id is still present and the normal index for action_id is removed. The foreign keys are still present, as they should. Is this normal behavior?
This is documented behaviour:


  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.


Multiple indexes over the same columns are allowed, but deprecated (as it is redundant- there is almost nothing that the non-unique key can do that the unique index cannot), and may error out in the future (although probably not for different index types like this case).

My suggestion, to prevent confusion, is to just create the unique index (or any explicit index that is adequate with a custom name) from the beginning. I agree with you, that this could be confusing, so looking at the manual is always a great idea, and I believe the current implementation make sense from a compromise between control and optimization perspective. :-)