sql - Why is MySQL not properly naming my foreign key?

Question

Please consider the following SQL code that I run in MySQL 8.0.22 (in an InnoDB database):

CREATE TABLE `person` (
  `person_id` smallint unsigned NOT NULL AUTO_INCREMENT, 
  `name` varchar(128) NOT NULL,
  PRIMARY KEY (`person_id`)
);

CREATE TABLE `pet` (
  `pet_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  PRIMARY KEY (`pet_id`)
);

ALTER TABLE `pet`
  ADD COLUMN `owner_id` smallint unsigned;

ALTER TABLE `pet`
  ADD CONSTRAINT `fk_pet_person`
  FOREIGN KEY `idx_fk_pet_person` (`owner_id`)
  REFERENCES `person` (`person_id`);

SHOW CREATE TABLE pet;

The output of SHOW CREATE TABLE pet is:

CREATE TABLE `pet` (
  `pet_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `owner_id` smallint unsigned DEFAULT NULL,
  PRIMARY KEY (`pet_id`),
  KEY `fk_pet_person` (`owner_id`),
  CONSTRAINT `fk_pet_person` FOREIGN KEY (`owner_id`) REFERENCES `person` (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

In the output above, why is the KEY named fk_pet_person when I specified its name as idx_fk_pet_person in my ALTER TABLE command? How can I get it to be named so?

Answer 1

You are confusing the FOREIGN KEY and the INDEX that makes it work. Pay attention - there is NO expression name (which looks like an index definition but is not) in the constraint definition displayed in your code.

When there is no suitable index for the constraint to work, then this index is auto-created, and the constraint name is used as the index name. If a suitable index exists, then auto-creation does not occur.

If you want the index to have a defined name then you must create this index in a separate ALTER TABLE (sub)statement before the constraint creation:

ALTER TABLE `pet`
  ADD KEY `idx_fk_pet_person` (`owner_id`),
  ADD CONSTRAINT `fk_pet_person`
  FOREIGN KEY (`owner_id`)
  REFERENCES `person` (`person_id`);

or

ALTER TABLE `pet`
  ADD KEY `idx_fk_pet_person` (`owner_id`);

ALTER TABLE `pet`
  ADD CONSTRAINT `fk_pet_person`
  FOREIGN KEY (`owner_id`)
  REFERENCES `person` (`person_id`);

DEMO