CODEWITHSUNDEEP
databasesqliteprimary-keynotnullrowid
Phoenix
Phoenix

Reputation: 383

sqlite integer primary key not null constraint failed

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?

Upvotes: 0

Views: 3181

Answers (2)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

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');

Upvotes: 0

CL.
CL.

Reputation: 180210

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

Upvotes: 3

Related Questions