Inconsistencies with ADD CONSTRAINT index_name in MySQL I created this example table

CREATE DATABASE foobar;
USE foobar;
CREATE TABLE foos (
  id INT(11) AUTO_INCREMENT PRIMARY KEY,
  firstname VARCHAR(30) NOT NULL,
  lastname VARCHAR(30) NOT NULL,
  bar_id INT(11) DEFAULT NULL
);
CREATE TABLE bars (
  id INT(11) AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL
);


Then ran this query

ALTER TABLE foos ADD CONSTRAINT constraint_name FOREIGN KEY foreign_key_name(bar_id) REFERENCES bars(id);


Some mysql server will return this on show create table foos

CREATE TABLE `foos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `bar_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `constraint_name` (`bar_id`),
  CONSTRAINT `constraint_name` FOREIGN KEY (`bar_id`) REFERENCES `bars` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


Then some other ones will return this on show create table foos

CREATE TABLE `foos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `bar_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `constraint_name` (`bar_id`),
  CONSTRAINT `foreign_key_name` FOREIGN KEY (`bar_id`) REFERENCES `bars` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


Notice the differences in the index_name, it should be foreign_key_name.

At first I thought it was a problem between MySQL 5.5 vs 5.6, but that does not seem to be the case.

Does anyone know why sometimes the index_name is ignored?
See if these sound close.  (Taken from Changelogs)

----- not yet released 5.7.6 Milestone 16 &amp; 2015-02-02 5.6.23 -- Bugs Fixed -- InnoDB -----

An ALTER TABLE ( http://dev.mysql.com/doc/refman/5.6/en/alter-table.html ) operation that changed the name of a foreign key column resulted in a failure when reloading the foreign key constraint. The previous column name remained in the data dictionary cache instead of being evicted. (Bug #20031243)

----- 2014-02-10 MariaDB 10.0.8 -- -- -----

MDEV-4439 ALTER TABLE .. [ADD|DROP] FOREIGN KEY IF [NOT] EXISTS does not work if constraint name is not used. Patches for server and the Innodb engine. Server is fixed so it does nothing if no indexes left to alter. Innodb parser is fixed so it looks for the IF [NOT] EXISTS option in a string. Another change is that it uses the index name for the internal dictionary. Prior to that it only used the CONSTRAINT name for it.

----- 2009-07-13 5.1.37 -- Bugs Fixed -- -----

Renaming a column that appeared in a foreign key definition did not update the foreign key definition with the new column name. (Bug #21704)

----- 2008-01-29 5.1.23 -- Bugs Fixed -- -----

User-supplied names foreign key names might not be set to the right key, leading to foreign keys with no name. (Bug #30747)