SQL: Create table with two indexes

Question

I previously created a table using the following SQL code:

CREATE TABLE myTable (
    id_A BIGINT NOT NULL,
    id_B INT NOT NULL,
    some_info varchar(255),
    some_info2 varchar(255),
    PRIMARY KEY (id_A) 
)

In the previous table created, both id_A and id_B would be unique values. I understand that id_A is forced to be unique by the PRIMARY KEY (id_A) code, which is perfect (which also indexes it), however id_B isn't

Here is where I am confused. id_B will also be unique, however I am not sure how to force it to be unique in the table,and how to make the database create an index for it so that future queries that use SELECT on this table will have good preformance. I know I can't have two PRIMARY KEYS:

PRIMARY KEY (id_A) 
PRIMARY KEY (id_B)

How might I go about making an index for id_B as well so that future queries happen efficiently?

I believe MySQL has the UNIQUE keyword you can put after the datatype. — SCFi, Apr 12 '16 at 17:14
Take a look at this: http://stackoverflow.com/questions/5038040/mysql-make-an-existing-field-unique — David Oesterreich, Apr 12 '16 at 17:22
I suggest you read up on primary keys. Primary keys are meant to be a row "identifier", rather than an index. — Tuncay Göncüoğlu, Apr 12 '16 at 17:26
@TuncayGöncüoğlu - In MySQL, a `PRIMARY KEY` is an index and it is UNIQUE and (for InnoDB) it is "clustered" with the data. — Rick James, Apr 13 '16 at 21:51
@RickJames I know. But thinking of them as indexes leads to the mistake poster has made. In one-column primary key it doesnt matter, but in multi-column primary keys, as in the question asked, the difference is quite clear, ie, the key is unique as a whole, not every column is unique. This is true for MySQL, as it is for all other RDBMSs. Hence my suggestion to read up on primary keys. — Tuncay Göncüoğlu, Apr 14 '16 at 14:09

score 4 · Accepted Answer · answered Apr 12 '16 at 17:15

4

You can add a UNIQUE INDEX on the column. While a table can only have one PRIMARY KEY, it can have as many UNIQUE INDEXes as you might need.

ALTER TABLE myTable
ADD UNIQUE INDEX `UI_myTable_idB` (`id_B` );

answered Apr 12 '16 at 17:15

Tom H

44,871
12
81
121

Great answer! is the `UI_myTable_idB` missing an underline? to become: `UI_myTable_id_B` – Webeng Apr 12 '16 at 17:48
It's just a name, so it doesn't matter whether or not it has the underline. That was just the naming convention that I used ('UI' + tablename without underscores + column name without underscores. You could name it however you want. – Tom H Apr 12 '16 at 17:58
1

oh I see, so it's the name of the new index created, and what's in the parenthesis is what links the code to the column you wish to index. Thanks mate! – Webeng Apr 12 '16 at 18:36
Why have two unique integer keys? Why not make one of them the `PRIMARY KEY` and get rid of the other column? – Rick James Apr 13 '16 at 21:52

score 2 · Answer 2 · answered Apr 12 '16 at 17:21

2

You can use the MySQL UNIQUE KEY for the column. This will force the values in the id_B column to be unique, but not use it as the primary key column. You can achieve it with this statement:

CREATE TABLE myTable (
id_A BIGINT NOT NULL,
id_B INT NOT NULL,
some_info varchar(255),
some_info2 varchar(255),
PRIMARY KEY (id_A),
UNIQUE KEY (id_b))

This will automatically index the column like you want it to. Primary keys and unique keys in a MySQL table are essentially the same, except a primary key cannot be NULL, and there can be only one primary key column (or combination of primary key columns). There can be any number of unique key columns.

answered Apr 12 '16 at 17:21

Matt

21
4

Would `UNIQUE KEY (id_b)` not only make the column unique but also index it? or do I have to use a line like `UNIQUE INDEX (id_b)` ? – Webeng Apr 12 '16 at 17:50
In MySQL key and index essentially mean the same thing. So no, you would not need to specify `UNIQUE INDEX (id_B)`. By using `UNIQUE KEY (id_B)` MySQL will do the indexing for you. – Matt Apr 12 '16 at 18:21
Thanks Matt, I upvoted your post. If I could accept (green check) two answers I would have also green-checked yours :) – Webeng Apr 12 '16 at 18:38

SQL: Create table with two indexes

2 Answers2