0

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?

Phoenix
  • 171
  • 2
  • 15
  • Seems the same as this [question](https://stackoverflow.com/q/49963559) (which comes with some good answers). – tanius Apr 26 '20 at 21:10
  • 1
    Does this answer your question? [Using a Primary Key with a WithoutRowID](https://stackoverflow.com/questions/49963559/using-a-primary-key-with-a-withoutrowid) – tanius Apr 26 '20 at 21:12

1 Answers1

3

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

CL.
  • 173,858
  • 17
  • 217
  • 259
  • To avoid confusions: "autoincrementing" in this answer is a broader term than SQLite's [AUTOINCREMENT](https://www.sqlite.org/autoinc.html) keyword. Both are only possible in ROWID tables, though. – tanius Apr 26 '20 at 21:16