Clever Geek Handbook

SQL: What does KEY id_2 do?

I used:

PRIMARY KEY (id), UNIQUE id (id), KEY id_2 (id)

whenever i create a table but i don't understand what it does KEY id_2 (id)

and why?

I've searched everywhere and can't find a suitable answer. Thank!

+3


source to share


4 answers


As per your example, I believe you are creating three different indexes on the same column ... which is probably not what you intended to do.

First, you create a primary key:

PRIMARY KEY (id)

Then you create a unique index that you don't need because you already have a primary key ... and you call that unique index id

:



UNIQUE id (id)

Finally, you create a third index named id_2

:

KEY id_2 (id)

Instead, I think you should only create the primary key and drop the other two indexes.

+3


source


From this question: Why do most SQL databases allow the same index to be quoted twice?

It looks like the syntax KEY

allows you to create a named index on that key column. id_2

is a name for him. As you will see in the related quesiton, you can specify this multiple times:



CREATE TABLE `testkey` (
  `id` varchar(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`),
  KEY `id_2` (`id`)
)
+1


source


This is the name of your index. You create index id_2 on id column

0


source


It creates an index on the column id

named id_2

.

From the documentation for the MySQL CREATE TABLE statement :

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition:
    col_name column_definition   | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...   | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      [index_option] ...

In other words, it KEY

is synonymous INDEX

.

0


source






More articles:


All Articles