1

I want to create an index using this query:

CREATE INDEX `PRIMARY` ON `elements` (`id`) USING BTREE

Or something like that, I've tried several queries:

1. CREATE INDEX `PRIMARY` ON `elements` (`id`) USING BTREE 2. CREATE INDEX 'PRIMARY' ON `elements` (`id`) USING BTREE 3. CREATE INDEX `PRIMARY` ON 'elements' (`id`) USING BTREE 4. CREATE INDEX PRIMARY ON elements (`id`) USING BTREE 5. CREATE INDEX PRIMARY ON elements (id) USING BTREE

I've also tried the 5 queries above without "USING BTREE". But I'm always getting this message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY ON 'elements' (`id`) USING BTREE' at line 1

This one works:

CREATE INDEX test ON elements (brandid);

I can't really discover a big difference between the two. Why can't I create an index with name PRIMARY?

Improve this question
8
  • 3
    PRIMARY its a Reserved Word Commented Jan 15, 2014 at 14:28
  • But I can create an index with that name when I use phpmyadmin, why is phpmyadmin able to use that word? Also, when I put in in quotes "", than it's a string, than it's not reserved anymore I think? Commented Jan 15, 2014 at 14:29
  • You can only use the name PRIMARY when creating a primary key. Commented Jan 15, 2014 at 14:34
  • @TiiJ7 Reserved words are permitted as identifiers if you quote them as described in Section 9.2, Shema Object Names: Thats dint aply to PRIMARY ?? Commented Jan 15, 2014 at 14:36
  • @Melon From the manual: The name of a PRIMARY KEY is always PRIMARY, which thus cannot be used as the name for any other kind of index. Commented Jan 15, 2014 at 14:39

1 Answer 1

Reset to default
2

PhpMyAdmin creates PRIMARY KEY (which is actually unique index) using syntax like this:

ALTER TABLE `elements` ADD PRIMARY KEY(id)

You can have only one PRIMARY KEY per table.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

thats to create primary key, he ask abaut index.
Thanks, I was hoping for another solution, but I'll have to go for this one since it's the only one that works.
@Melon I know what he is asking, but I have tried to create simple, one-column index called PRIMARY on table that already have PRIMARY KEY and I get error #1068 - Multiple primary key defined , which means that phpMyAdmin actually tries to create PRIMARY Key when instructed to create index named PRIMARY.
@Melon No problem, I hope it gets better :)