Parse error: near "autoincrement": syntax error - SQLite

Question

I get a syntax error near AUTOINCREMENT. What is the cause of this error?

CREATE TABLE person (
    id INTEGER NOT NULL AUTOINCREMENT,
    name TEXT NOT NULL
);

CREATE TABLE department (
    id INTEGER NOT NULL AUTOINCREMENT,
    name TEXT NOT NULL,
    FOREIGN KEY (leader) REFERENCES person(id)
);

Nollie

Answer 1

According to SQLite FAQ you have to declare either a INTEGER PRIMARY KEY or INTEGER PRIMARY KEY AUTOINCREMENT column to achieve that.

Lucre

Answer 2

In SQLite you need not to specify AUTOINCREMENT if you are specifying a column as Primary Key...

Philippopolis

Answer 3

SQLite AUTOINCREMENT : You Should Avoid Using It

Unless you create a table specifying the WITHOUT ROWID option, you get an implicit auto increment column called rowid.

The rowid column store 64-bit signed integer that uniquely identifies a row within the table.

Claver

Also, do select rowid, * from table_name to retrieve the rowid as well. – Equator

Answer 4

I got the same error below:

Parse error: near "autoincrement": syntax error

Because I used AUTOINCREMENT without INTEGER PRIMARY KEY as shown below:

CREATE TABLE my_table (
  id INTEGER AUTOINCREMENT
          -- ↑↑↑↑↑↑↑↑↑↑↑↑↑
);

So, I used AUTOINCREMENT with INTEGER PRIMARY KEY as shown below, then the error was solved:

CREATE TABLE my_table (
  id INTEGER PRIMARY KEY AUTOINCREMENT
  -- ↑↑↑↑↑↑↑ ↑↑↑↑↑↑↑ ↑↑↑
);

The doc says below:

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.

In addition, if you use TEXT type with PRIMARY KEY AUTOINCREMENT as shown below:

CREATE TABLE my_table (
  id TEXT PRIMARY KEY AUTOINCREMENT
  -- ↑↑↑↑
);

Then, you get the error below:

Parse error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY

Colubrid

Answer 5

It's an easy solution. Just use AUTOINCREMENT instead of AUTO_INCREMENT

Mosa

They are using AUTOINCREMENT. – Preventive