Is index_type ignored on MySQL primary keys in create table? I have seen a create table whose primary key had an identifier where it should have an index_type, something like that:

create table a (foo INTEGER, PRIMARY KEY foo_id (foo));


The thing is that according to documentation it shouldn't be allowed, as where foo_id is should be, if something, an index_type (USING {BTREE | HASH}).

If I run a show create table for this table foo_id is removed.

Does MySQL ignore it or I'm missing something?
You cannot name a PRIMARY KEY. The name PRIMARY KEY is the designation of the arbitrary, or preferred, unique index (candidate key) for accessing the table. A table can have multiple unique keys, but only one PRIMARY KEY.

EXAMPLE: Employee table with three unique keys


EmployeeID
Driver's License Number
Social Security Number


You can pick a name out of a hat, flip a coin, or ask project managers which one of the three indexes should be the PRIMARY KEY.

You can create a unique index without it being the PRIMARY KEY as follows:

create table a (foo INTEGER, UNIQUE KEY foo_id (foo)); 


Check these other links about PRIMARY KEYs vs Unique Keys


Why do primary keys have names of their own?
What Is The Point of a Primary Key?


As for the other question: the Index Type

When it comes to the Index Type, here are the defaults:


The MEMORY storage engine and NDB (MySQL Cluster) use HASH as the index_type.


You could specify BTREE as the index type
It tends to bloat more than HASH indexes

The index_type for MyISAM and InnoDB is BTREE.


MyISAM and InnoDB DO NOT SUPPORT HASH INDEXES
You have to emulate HASH indexing



To find out the index type, run this query:

select index_name,index_type from information_schema.statistics
where table_schema='test' and table_name='a';


Here is an example:

mysql&gt; use test
Database changed
mysql&gt; create table a (foo INTEGER, PRIMARY KEY foo_id (foo));
Query OK, 0 rows affected (0.07 sec)

mysql&gt; show create table a\G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `foo` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`foo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql&gt; select index_name,index_type from information_schema.statistics
    -&gt; where table_schema='test' and table_name='a';
+------------+------------+
| index_name | index_type |
+------------+------------+
| PRIMARY    | BTREE      |
+------------+------------+
1 row in set (0.00 sec)

mysql&gt;