AUTOINCREMENT in SQLite3

Question

SQL

I'm trying to create a simple table in SQLITE3:

CREATE TABLE IF NOT EXISTS `tbl` (
  `id` int(3) NOT NULL AUTOINCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

And nothing is created, swears at AUTOINCREMENT. Initially, I wrote AUTO_INCREMENT, as in MySQL, then I found the documentation, which states that it is written without underlining. No more sense, near "AUTOINCREMENT": syntax error. Tell me what am I doing wrong?

Answer 1

Here is a query that works.

CREATE TABLE IF NOT EXISTS `tbl` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,
  `name` varchar(255) NOT NULL);

A small recipe for the future to solve such problems: create a table of the desired type with a wizard and look at the creation script.

Answer 2

In general, to learn the SQL syntax that is used in SQLite, you need to read http://sqlite.org/lang.html .
In particular, at http://sqlite.org/lang_createtable.html you can see the "column-constraint" diagram, according to which AUTOINCREMENT is a modifier for PRIMARY KEY.

Answer 3

According to the SQLite FAQ , INTEGER PRIMARY KEY may not be declared as AUTOINCREMENT.
Quote:
How do I create an AUTOINCREMENT field.
Short answer: A column declared INTEGER PRIMARY KEY will autoincrement.