> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/reference/error-codes/mariadb-error-codes-1000-to-1099/e1075.md).

# Error 1075: Incorrect table definition; there can be only one auto column and it must be defined as

| Error Code | SQLSTATE | Error                | Description                                                                                   |
| ---------- | -------- | -------------------- | --------------------------------------------------------------------------------------------- |
| 1075       | 42000    | ER\_WRONG\_AUTO\_KEY | Incorrect table definition; there can be only one auto column and it must be defined as a key |

## Possible Causes and Solutions

A table can have only one [AUTO\_INCREMENT](/docs/server/reference/data-types/auto_increment.md) column, and the column must be indexed. [CREATE TABLE](/docs/server/server-usage/tables/create-table.md) and [ALTER TABLE](/docs/server/reference/sql-statements/data-definition/alter/alter-table.md) fail with this error when:

* The table has more than one `AUTO_INCREMENT` column.
* The `AUTO_INCREMENT` column has no index.
* For InnoDB, no index starts with the `AUTO_INCREMENT` column. [MyISAM](/docs/server/server-usage/storage-engines/myisam-storage-engine.md) and [Aria](/docs/server/server-usage/storage-engines/aria.md) also accept the column as a later part of a multi-column index, and then number the rows separately for each value of the columns before it.
* An `ALTER TABLE` drops the only index of the `AUTO_INCREMENT` column, for example the primary key.

For example:

```
CREATE OR REPLACE TABLE animals (
    id1 MEDIUMINT NOT NULL AUTO_INCREMENT,
    id2 TINYINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (id1),
    KEY (id2)
);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

CREATE OR REPLACE TABLE animals (
    id1 MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL
);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

CREATE OR REPLACE TABLE animals (
    grp INT NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    KEY (grp, id)
) ENGINE=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

CREATE OR REPLACE TABLE animals (
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (id)
);
Query OK, 0 rows affected (0.017 sec)

ALTER TABLE animals DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
```

To solve the error, keep one `AUTO_INCREMENT` column, and make it the primary key or the first column of an index. To replace the primary key of the `AUTO_INCREMENT` column, add the new index in the same statement, for example `ALTER TABLE animals DROP PRIMARY KEY, ADD UNIQUE (id)`.

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}