Error
Error Code:
1164
MariaDB Error 1164: Table Type Lacks Auto-Increment
Description
This error indicates that you have attempted to define an AUTO_INCREMENT column in a table whose chosen storage engine does not support this feature. It typically arises when executing CREATE TABLE or ALTER TABLE statements.
Error Message
The used table type doesn't support AUTO_INCREMENT columns
Known Causes
3 known causesMEMORY Engine Usage
The MEMORY (HEAP) storage engine is designed for temporary, in-memory tables and explicitly does not support AUTO_INCREMENT columns.
CSV Engine Usage
The CSV storage engine, which stores data in plain text files, does not provide support for AUTO_INCREMENT functionality.
ARCHIVE Engine Usage
The ARCHIVE storage engine, optimized for data archiving, does not include support for AUTO_INCREMENT columns.
Solutions
3 solutions available1. Change Table Engine to InnoDB easy
Modify the table to use the InnoDB storage engine, which supports AUTO_INCREMENT.
1
Identify the table causing the error. You can usually infer this from the context of your SQL query or application logs.
2
Alter the table to change its storage engine to InnoDB.
ALTER TABLE your_table_name ENGINE=InnoDB;3
If you are creating a new table, specify the engine during table creation.
CREATE TABLE your_table_name (...) ENGINE=InnoDB;2. Remove AUTO_INCREMENT Constraint easy
If AUTO_INCREMENT is not essential, remove it from the column definition.
1
Identify the column that has the AUTO_INCREMENT attribute and is causing the error.
2
Alter the table to remove the AUTO_INCREMENT attribute from the specified column. This is useful if you intend to manually manage the primary key values.
ALTER TABLE your_table_name MODIFY your_column_name INT;3
If you are creating a new table, simply omit the AUTO_INCREMENT keyword when defining the column.
CREATE TABLE your_table_name (your_column_name INT PRIMARY KEY, ...);3. Convert MyISAM Table to InnoDB medium
Perform a conversion of an existing MyISAM table to InnoDB, preserving data and structure.
1
Check the current engine of your table. If it's MyISAM, this solution is applicable.
SHOW CREATE TABLE your_table_name;2
Create a new table with the same structure but with InnoDB engine.
CREATE TABLE your_table_name_new LIKE your_table_name;3
Set the engine of the new table to InnoDB.
ALTER TABLE your_table_name_new ENGINE=InnoDB;4
Copy the data from the old MyISAM table to the new InnoDB table.
INSERT INTO your_table_name_new SELECT * FROM your_table_name;5
Drop the old MyISAM table.
DROP TABLE your_table_name;6
Rename the new InnoDB table to the original table name.
RENAME TABLE your_table_name_new TO your_table_name;