Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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`)
) 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>
share|improve this answer
add comment

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.

share|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. –  BryceAtNetwork23 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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