SQLite创建WITHOUT ROWID表报错：主键约束禁止使用表达式

执行以下SQLite CREATE TABLE语句时出现错误：

CREATE TABLE IF NOT EXISTS "history" 
(
    "inscode" INTEGER NOT NULL,
    "yyyyymmdd" INTEGER NOT NULL,
    "basic_data" INTEGER NOT NULL,
    "instrument_state" INTEGER NOT NULL,
    "static_thresholds" INTEGER NOT NULL,
    "trades" INTEGER NOT NULL,
    "closing_price" INTEGER NOT NULL,
    "best_limits" INTEGER NOT NULL,
    "client_type" INTEGER NOT NULL,
    "shareholders" INTEGER NOT NULL,
    PRIMARY KEY("inscode", "yyyymmdd")
) WITHOUT ROWID

收到的错误信息为：

Execution finished with errors.

Result: expressions prohibited in PRIMARY KEY and UNIQUE constraints

问题原因 #

问题出在主键约束的列名拼写错误：表结构中定义的日期列是yyyyymmdd（四个y），但主键约束里写的是yyyymmdd（三个y）。SQLite找不到名为yyyymmdd的列，会将其判定为表达式，而WITHOUT ROWID表不允许在主键或唯一约束中使用表达式，因此触发该错误。

修正后的SQL语句 #

CREATE TABLE IF NOT EXISTS "history" 
(
    "inscode" INTEGER NOT NULL,
    "yyyyymmdd" INTEGER NOT NULL,
    "basic_data" INTEGER NOT NULL,
    "instrument_state" INTEGER NOT NULL,
    "static_thresholds" INTEGER NOT NULL,
    "trades" INTEGER NOT NULL,
    "closing_price" INTEGER NOT NULL,
    "best_limits" INTEGER NOT NULL,
    "client_type" INTEGER NOT NULL,
    "shareholders" INTEGER NOT NULL,
    PRIMARY KEY("inscode", "yyyyymmdd")
) WITHOUT ROWID

内容的提问来源于stack exchange，提问作者Dariush Eivazi