sqlite integer primary key not null constraint failed

Question

According to the SQLite documentation / FAQ a column declared INTEGER PRIMARY KEY will automatically get a value of +1 the highest of the column if omitted.
Using SQLite version 3.22.0 2018-01-22 18:45:57
Creating a table as follows:

CREATE TABLE test (
  demo_id INTEGER PRIMARY KEY NOT NULL,
  ttt VARCHAR(40) NOT NULL,
  basic VARCHAR(25) NOT NULL,
  name VARCHAR(255) NOT NULL,
  UNIQUE(ttt, basic) ON CONFLICT ROLLBACK
) WITHOUT ROWID;

Then inserting like this:

INSERT INTO test (ttt, basic, name) VALUES ('foo', 'bar', 'This is a test');

gives:

Error: NOT NULL constraint failed: test.demo_id
sqlite>

When it is expected to create a record with a demo_id value of 1. Even if the table already contains values, it'll fail inserting the row without explicitly specifying the id with the same error.

What am I doing wrong?

Auction

Seems the same as this question (which comes with some good answers). – Sofiasofie

Does this answer your question? Using a Primary Key with a WithoutRowID – Sofiasofie

Answer 1

The documentation says that you get autoincrementing values for the rowid. But you specified WITHOUT ROWID.

Gimmick

To avoid confusions: "autoincrementing" in this answer is a broader term than SQLite's AUTOINCREMENT keyword. Both are only possible in ROWID tables, though. – Sofiasofie

Answer 2

If you create a table with WITHOUT ROWID as shown below:

CREATE TABLE person (
  id INTEGER PRIMARY KEY,
  name TEXT
) WITHOUT ROWID;

Then, you need to specify the value for the PRIMARY KEY column to insert a row because auto-increment doesn't work:

                    ↓↓                ↓
INSERT INTO person (id, name) VALUES (3, 'John');

Or:

                           ↓
INSERT INTO person VALUES (3, 'John');

In addition, you can manually create auto-increment as shown below:

INSERT INTO person (id, name) 
VALUES ((SELECT IFNULL(MAX(id), 0) + 1 FROM person), 'John');

Or:

INSERT INTO person 
VALUES ((SELECT IFNULL(MAX(id), 0) + 1 FROM person), 'John');

Loosen