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

As for the other question: the Index Type

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

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`)
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)

| improve this answer | |

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


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.

| improve this answer | |
  • Yes, I saw it, the thing is that in contrast to UNIQUE, PRYMARY keys cannot have a name, but you still can specify the index type. What it doesn't make any sense to me is that it accepts a name where should be a type, and totally ignore it. – Jaime Soriano Dec 21 '11 at 16:57
  • Agreed; it should not allow you to add a value that it's not going to use. – Aaron Dec 21 '11 at 17:22
  • 2
    MySQL has several places where it allows some things (especially in DDL) and simply ignores them. It is supposingly for scripts to be migrated from other DBMS with minimal changes. – ypercubeᵀᴹ Apr 18 '12 at 20:07
  • Name of PK, CHECK constraints, inline FKs, order of indexes (ASC, DESC) are a few that come to mind. – ypercubeᵀᴹ Apr 18 '12 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.