Is index_type ignored on MySQL primary keys in create table?

Question

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?

Answer 1

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> use test
Database changed
mysql> create table a (foo INTEGER, PRIMARY KEY foo_id (foo));
Query OK, 0 rows affected (0.07 sec)

mysql> 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> select index_name,index_type from information_schema.statistics
    -> where table_schema='test' and table_name='a';
+------------+------------+
| index_name | index_type |
+------------+------------+
| PRIMARY    | BTREE      |
+------------+------------+
1 row in set (0.00 sec)

mysql>

Answer 2

Not sure, but if you wanted to verify that it's still there, you could try this:

SHOW INDEXES FROM a;

This shows "PRIMARY" for the key_name. It must disregard the name and use that instead. Though, for other key types (ex: UNIQUE) it does preserve name. Interesting.