Hi,
my environment is as follow:
Code:
mysql> show variables where variable_name 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)
this is my create table statement
Code:
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'
);
this is what I get from show create table
Code:
mysql> show create table test1;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------+
| Table | Create Table

                                                                          |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
  `test1_id` int(11) NOT NULL DEFAULT '0',
  `test1_string` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`test1_id`) USING HASH COMMENT 'primary key index for test1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
as you realize the name of the primary key constraint is not effective.

Code:
mysql> select CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA, CONSTRAINT_TYPE
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME='test1';
+-------------------+-----------------+--------------+-----------------+
| CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | CONSTRAINT_TYPE |
+-------------------+-----------------+--------------+-----------------+
| certify           | PRIMARY         | certify      | PRIMARY KEY     |
+-------------------+-----------------+--------------+-----------------+
1 row in set (0.02 sec)
why is this so? from MySQL :: MySQL 5.5 Reference Manual :: 13.1.17 CREATE TABLE Syntax, I doubt that there's anything wrong with my syntax.

also there's another issue:

Code:
mysql> show index from test1;
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+----------------------
-------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi
nality | Sub_part | Packed | Null | Index_type | Comment | Index_comment
       |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+----------------------
-------+
| test1 |          0 | PRIMARY  |            1 | test1_id    | A         |
     0 |     NULL | NULL   |      | BTREE      |         | primary key index for
 test1 |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+----------------------
-------+
1 row in set (0.00 sec)


if you take a close look, you will realize that index type is BTREE and not HASH as desired. why is this so, is there something wrong with my syntax?

thanks for any guidance!