Why does MariaDB create another key for this column when it is already part of the primary key?

All we need is an easy explanation of the problem, so here it is.

create table foo (
  id int primary key
);

create table bar (
  a int references foo(id),
  b int references foo(id),
  primary key (a, b)
);

show index from bar;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| bar   |          0 | PRIMARY  |            1 | a           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| bar   |          0 | PRIMARY  |            2 | b           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| bar   |          1 | b        |            1 | b           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Fiddle: https://dbfiddle.uk/?rdbms=mariadb_10.6&fiddle=ebd524aa5702fd8c75ef140e5e9264ea

Where does the additional key on b come from? Is this a bug? This does not happen when using MySQL.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

A FOREIGN KEY implicitly creates an index if there is not one already in existence. The index exists to make the FK constraint efficient to check.

PRIMARY KEY(a,b)  -- suffices to provide an index for `a`
INDEX(b)  -- generated by the FK

I believe that this has existed in all versions of MySQL and MariaDB when using ENGINE=InnoDB.

Could it be that your 10.5 defaulted to ENGINE=MyISAM? That Engine ignores FK specifications such as references(...). Note that the "default engine" has changed over time — I think InnoDB was the default starting with version 5.5.

Method 2

The additional index/key is created because the multi-column primary key cannot be used to look up rows when just a "b" is given. See the comments on the question and https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html for more info.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply