I am trying to create a new table with a primary key that has 2 columns however I get the following error:
Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key
CREATE TABLE `table1` (
`id` int AUTO_INCREMENT NOT NULL,
`entity_id` varchar(36) NOT NULL,
`entity_type` varchar(36) NOT NULL,
`score` decimal(4,3) NOT NULL,
`raw` json DEFAULT NULL,
`date` date NOT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`deleted_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`date`,`id`),
KEY `table1_indx` (`date`,`score`,`entity_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
The error message suggests that I have more than 1 auto column. Whereas I have only defined id as an auto-increment. Am I misinterpreting the meaning of this error?
I have defined my primary key as (date, id).
The error is a bit misleading, but it's referring to this rule:
https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html
To use the AUTO_INCREMENT mechanism with an InnoDB table, an AUTO_INCREMENT column must be defined as the first or only column of some index such that it is possible to perform the equivalent of an indexed SELECT MAX(ai_col) lookup on the table to obtain the maximum column value. The index is not required to be a PRIMARY KEY or UNIQUE, but to avoid duplicate values in the AUTO_INCREMENT column, those index types are recommended.
You could fix the table you show by making the auto-increment the first column of the compound primary key:
...
PRIMARY KEY (`id`, `date`),
...
Note the order of columns in the key definition doesn't have to be the same as the order of columns in the table.
Alternatively, you could define another key, even a non-unique index, with the id as its first column:
...
PRIMARY KEY (`date`, `id`),
KEY (`id`),
...
I can guess that you intended the auto-increment id to number from 1 for each distinct date. But that's not how auto-increment works in InnoDB. There is only one auto-increment counter for the table.