I'm trying to add some values into a table, but I get an error for not manually inputting the id. When I remove 'not null' the id just stays null Answer The answer Shawn gave worked, which is to remove AUTO_INCREMENT https://sqlite.org/autoinc.html

Insert into table where id is not null and auto increment sqlite3

I’m trying to add some values into a table, but I get an error for not manually inputting the id.

sqlite> CREATE TABLE history (
   ...>     id INTEGER AUTO_INCREMENT NOT NULL,
   ...>     user_id INTEGER NOT NULL,
   ...>     symbol TEXT NOT NULL,
   ...>     price NUMERIC NOT NULL,
   ...>     shares INTEGER NOT NULL,
   ...>     timestamp DATETIME NOT NULL,
   ...>     type TEXT NOT NULL,
   ...>     PRIMARY KEY(id),
   ...>     FOREIGN KEY(user_id) REFERENCES users(id)
   ...> );
sqlite> INSERT INTO history (user_id,symbol,price,shares,timestamp,type) VALUES (1,'NFLX',202.83,2,'2022/06/09 02:14:25','buy');
Runtime error: NOT NULL constraint failed: history.id (19)

When I remove ‘not null’ the id just stays null

sqlite> CREATE TABLE history (
   ...>     id INTEGER AUTO_INCREMENT,
   ...>     user_id INTEGER,
   ...>     symbol TEXT NOT NULL,
   ...>     price NUMERIC NOT NULL,
   ...>     shares INTEGER NOT NULL,
   ...>     timestamp DATETIME NOT NULL,
   ...>     type TEXT NOT NULL,
   ...>     PRIMARY KEY(id),
   ...>     FOREIGN KEY(user_id) REFERENCES users(id)
   ...> );
sqlite> INSERT INTO history (user_id,symbol,price,shares,timestamp,type) VALUES (1,'NFLX',202.83,2,'2022/06/09 02:14:25','buy');
sqlite> SELECT * FROM history;
+----+---------+--------+--------+--------+---------------------+------+
| id | user_id | symbol | price  | shares |      timestamp      | type |
+----+---------+--------+--------+--------+---------------------+------+
|    | 1       | NFLX   | 202.83 | 2      | 2022/06/09 02:14:25 | buy  |
+----+---------+--------+--------+--------+---------------------+------+

Answer

The answer Shawn gave worked, which is to remove AUTO_INCREMENT https://sqlite.org/autoinc.html

CREATE TABLE history (
    id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    symbol TEXT NOT NULL,
    price NUMERIC NOT NULL,
    shares INTEGER NOT NULL,
    timestamp DATETIME NOT NULL,
    type TEXT NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(user_id) REFERENCES users(id)
);

Advertisement

Answer