我的python+sqlite3代码在创建表时有什么问题？

Question

我正在尝试创建一个数据库，其中有几个表使用sqlite3的外键相互连接，我正在用python编写。你知道吗

这是我的密码：

db = sqlite3.connect("PHLC.db")
cur = db.cursor()

# ############################
# delete original table if exist
# drop from the end (foreign key issue)
cur.execute("drop table if exists measurement")
cur.execute("drop table if exists mouse")
cur.execute("drop table if exists drug")
cur.execute("drop table if exists batch")
cur.execute("drop table if exists phlc")

# ############################
# create table
# ############################
# 1. phlc
cur.execute(
    """
    CREATE TABLE phlc (
    phlc_id INTEGER NOT NULL PRIMARY KEY,
    cancer VARCHAR(30) NOT NULL,
    histology VARCHAR(60) NOT NULL
    )
    """
)
# 2. batch
cur.execute(
    """
    CREATE TABLE batch (
    batch_id INTEGER PRIMARY KEY AUTOINCREMENT,
    phlc_id INTEGER NOT NULL,
    FOREIGN KEY (phlc_id) REFERENCES phlc (phlc_id),
    batch_number INTEGER NOT NULL
    )
    """
)
# 3. drug
cur.execute(
    """
    CREATE TABLE drug (
    drug_id INTEGER PRIMARY KEY AUTOINCREMENT,
    drug_name VARCHAR(30) NOT NULL,
    batch_id INTEGER NOT NULL,
    FOREIGN KEY (batch_id) REFERENCES batch (batch_id)
    )
    """
)
# 4. mouse
cur.execute(
    """
    CREATE TABLE mouse (
    mouse_id INTEGER PRIMARY KEY AUTOINCREMENT,
    drug_id INTEGER NOT NULL,
    FOREIGN KEY (drug_id) REFERENCES drug (drug_id)
    )
    """
) 
# 5. measurement
cur.execute(
    """
    CREATE TABLE measurement (
    measurement_index INTEGER PRIMARY KEY AUTOINCREMENT,
    mouse_id INTEGER NOT NULL,
    FOREIGN KEY (mouse_id) REFERENCES mouse (mouse_id),
    day INTEGER NOT NULL,
    tumor_volume FLOAT NOT NULL,
    comment VARCHAR(255) NULL
    )
    """
) 

db.commit()
db.close()

我得到的错误在批处理表中：

sqlite3.OperationalError: near "batch_number": syntax error

有人能指出代码的问题吗？（MySQL运行良好……）

Answer 1

根据documentation，任何表约束必须位于所有列定义之后：

CREATE TABLE batch (
    batch_id INTEGER PRIMARY KEY AUTOINCREMENT,
    phlc_id INTEGER NOT NULL,
    batch_number INTEGER NOT NULL,
    FOREIGN KEY (phlc_id) REFERENCES phlc (phlc_id)
)

或者，将外键声明设为列约束：

CREATE TABLE batch (
    batch_id INTEGER PRIMARY KEY AUTOINCREMENT,
    phlc_id INTEGER NOT NULL REFERENCES phlc (phlc_id),
    batch_number INTEGER NOT NULL
)