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?Why do primary keys have names of their own?
- What Is The Point of a Primary Key?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 INDEXESMyISAM and InnoDB DO NOT SUPPORT HASH INDEXES
You have to emulate HASH indexingYou 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>
