插入ID不为空的表并自动递增sqlite3

6fe3ivhb 于 2023-01-21 发布在 SQLite

关注(0)|答案(2)|浏览(162)

我试图向表中添加一些值，但是我收到一个错误，因为没有手动输入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)

当我删除'not null'时，id仍然为空

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  |
+----+---------+--------+--------+--------+---------------------+------+

sqlite

来源：https://stackoverflow.com/questions/72554160/insert-into-table-where-id-is-not-null-and-auto-increment-sqlite3

2条答案

按热度按时间

2skhul331#

Shawn给出的答案起作用了，那就是删除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)
);

赞(0）回复(0）举报 2023-01-21

bybem2ql2#

您不能设置（插入）主AUTOINCREMENT列的值，也不建议使用“AUTOINCREMENT”，正确的语法可以完全满足您的需要：