Why am I not able to name index differently from foreign constraint name

[Chun Hung Leng]

Hi

my environment is as follow:

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 1.1.8                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.27                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86                          |
| version_compile_os      | Win32                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

create table if not exists test1(
test1_id int,
test1_string varchar(10),
constraint pk_test1 primary key (test1_id) using hash comment 'primary key index for test1'
);

create table if not exists test1_child(
test1_child_id int,
test1_id int not null,
test1_child_string varchar(10),
constraint fk_test1_child_test_id foreign key fk_test1_child_test_id (test1_id) references test1 (test1_id)
);

next I decided to have different index name and foreign key constraint_name on test1_child, so I did the following:

from http://dev.mysql.com/doc/refman/5.5/en/alter-table.html

alter table test1_child drop foreign key fk_test1_child_test_id;

alter table test1_child drop index fk_test1_child_test_id;


alter table test1_child add constraint fk_test1_child_test_id foreign key fk_test1_child_test_id_index (test1_id) references test1(test1_id);

however results does not show what I expect

mysql> show index from test1_child;
+-------------+------------+------------------------+--------------+------------
-+-----------+-------------+----------+--------+------+------------+---------+--
-------------+
| Table       | Non_unique | Key_name               | Seq_in_index | Column_name
 | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | I
ndex_comment |
+-------------+------------+------------------------+--------------+------------
-+-----------+-------------+----------+--------+------+------------+---------+--
-------------+
| test1_child |          1 | fk_test1_child_test_id |            1 | test1_id
 | A         |           0 |     NULL | NULL   |      | BTREE      |         |
             |
+-------------+------------+------------------------+--------------+------------
-+-----------+-------------+----------+--------+------+------------+---------+--

from the referential_constraints

mysql> select CONSTRAINT_SCHEMA, CONSTRAINT_NAME, UNIQUE_CONSTRAINT_SCHEMA, UNIQ
UE_CONSTRAINT_NAME, REFERENCED_TABLE_NAME from INFORMATION_SCHEMA.REFERENTIAL_CO
NSTRAINTS where table_name='test1_child';
+-------------------+------------------------+--------------------------+-------
-----------------+-----------------------+
| CONSTRAINT_SCHEMA | CONSTRAINT_NAME        | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE
_CONSTRAINT_NAME | REFERENCED_TABLE_NAME |
+-------------------+------------------------+--------------------------+-------
-----------------+-----------------------+
| certify           | fk_test1_child_test_id | certify                  | PRIMAR
Y                | test1                 |
+-------------------+------------------------+--------------------------+-------
-----------------+-----------------------+
1 row in set (0.00 sec)

as you will have observe the foreign key constraint name is the same as the index_name, could anyone explain?

thanks a lot!