Re: table with multiple primary key

[Rick James]

> since auto_increment is allowed only in a PK

False. (I have to admit that I thought that was true for many years.)

The only requirement on indexing an auto_inc is that it be the _first_ column in _some_ index, not necessarily PK or even UNIQUE.

Normally, the AUTO_INCREMENT is not specified during an INSERT, in which case the value increments ordinarily. However, one could specify the AI field in an INSERT. At this point, one could violate the uniqueness if all you had was INDEX(id).

The index is necessary so that, when the table is opened, the engine can find the next value to use. That can be done with any kind of index, assuming the id is first.

Back to Osmar's case. This should work:
ALTER TABLE test DROP PRIMARY KEY, ADD PRIMARY KEY (date), ADD INDEX(id);

There are obscure reasons why you might want that combination, but I suspect Osmar does not have such a case.

First of all, it is _usually_ bad to have any kind of time or float datatype as the PRIMARY KEY or a UNIQUE KEY. This is because times and floats tend to have duplicate values.

On the other hand, if this table is designed to have exactly one row per "date", then I would suggest
CREATE TABLE test (
`date` DATE,
...
PRIMARY KEY(`date`) );
That is, get rid of the auto_increment and simply use the `date` as the PK.

Every table should have a PRIMARY KEY. In some cases, there is a "natural" PK, such as what I just described. In cases where there is not a "natural" PK, adding this is a good idea:
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

There is a gray area between a 'natural' PK and an artificial AI... If the natural PK is lengthy (say, more than 20 bytes), it may be more efficient to have that as a UNIQUE key, and add an AI. Then use the AI for JOINing to other tables. (This is a variant of "normalization".)