AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY - SQL

Question

I'm trying to create a simple table using DBeaver and is giving me this error, any hints? Thanks.

SQL Error [1]: [SQLITE_ERROR] SQL error or missing database (AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY)

CREATE TABLE users (
id          INT (11) NOT NULL PRIMARY KEY AUTOINCREMENT,
usermane    VARCHAR (255) NOT NULL,
password    VARCHAR (255) NOT NULL
);

use [AUTO_INCREMENT](https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html) (with an underscore...) — Luuk, Mar 31 '22 at 16:06
Related: https://stackoverflow.com/questions/7337882/what-is-the-difference-between-sqlite-integer-data-types-like-int-integer-bigi#:~:text=However%2C%20the%20dynamic%20typing%20in,an%208%2Dbyte%20long%20long. — Barmar, Mar 31 '22 at 16:18
Related [docs](https://www.sqlite.org/autoinc.html) "Because AUTOINCREMENT keyword changes the behavior of the ROWID selection algorithm, AUTOINCREMENT is not allowed on WITHOUT ROWID tables or on any table column other than INTEGER PRIMARY KEY. Any attempt to use AUTOINCREMENT on a WITHOUT ROWID table or on a column other than the INTEGER PRIMARY KEY column results in an error." — Luuk, Mar 31 '22 at 16:34

score 3 · Answer 1 · answered Mar 31 '22 at 16:09

The error says "SQLITE_ERROR" which means it comes from SQLite, not MySQL.

Perhaps you are using a CREATE TABLE statement designed for MySQL, and executing it against SQLite?

Anyway, SQLite requires you define an autoincrement field as INTEGER, not INT.

And you don't need to use the AUTO_INCREMENT keyword at all. In SQLite, the auto-increment behavior is implied by using INTEGER. You may use the keyword AUTOINCREMENT (with no underscore character, unlike MySQL's keyword), but you should read the documentation about the change in behavior when you use this keyword, and make sure it's what you want: https://sqlite.org/autoinc.html

sqlite> CREATE TABLE users (
   ...> id          INT (11) NOT NULL PRIMARY KEY AUTOINCREMENT,
   ...> usermane    VARCHAR (255) NOT NULL,
   ...> password    VARCHAR (255) NOT NULL
   ...> );
Error: in prepare, AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY (1)

sqlite> CREATE TABLE users (
   ...> id INTEGER NOT NULL PRIMARY KEY,
   ...> username VARCHAR(255) NOT NULL,
   ...> password VARCHAR(255) NOT NULL
   ...> );
sqlite>

(Notice no error the second time, therefore the create succeeded.)

P.S. I also corrected "usermane" to "username" which I suppose is what you want.

AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY - SQL

1 Answers1