0
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.

8
  • 1
    Just a guess, if you delete something from foo it has to check if there is a b in bar that references it. There is no need to do anything for a since the primary key can be used for that. MySQL/MARIADB use of the word key is confusing, index would have been a better word.
    – Lennart
    Jun 9 at 18:36
  • Checking your fiddle, this behaviour starts with 10.5. You may see the same in a later version of MYSQL.
    – Lennart
    Jun 9 at 18:40
  • 3
    Does this answer your question? Unique constraint replaces existing foreign key
    – mustaccio
    Jun 9 at 18:57
  • @Lennart I'm not sure how composite primary keys are implemented. Can the primary key be used to lookup values of just "a" because "a" comes first in the primary key? If that's the case, then the additional key/index on "b" makes sense. I tried swapping the columns in the primary key which causes Maria to create an additional key/index on "a" instead, which supports this theory.
    – jhenninger
    Jun 9 at 19:28
  • @mustaccio it's related but doesn't really answer the question. He is kinda asking the opposite ;)
    – jhenninger
    Jun 9 at 19:28

2 Answers 2

2

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.

1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.