JDBC入门遇SQLite建表错误：Visit表外键语法报错求助

我一眼就看出问题所在了——你的SQL语句里把表级FOREIGN KEY约束插在了列定义的中间，这不符合SQLite的语法规则。SQLite要求表级约束（比如单独声明的FOREIGN KEY）必须放在所有列定义的后面，不能穿插在列与列之间，这就是你遇到syntax error的核心原因。

修正方案一：使用列级外键约束 #

直接把外键关联逻辑写在对应列的定义后面，写法更简洁直观：

String sqlVisit = "CREATE TABLE IF NOT EXISTS Visit (" + 
    "id INTEGER PRIMARY KEY NOT NULL," + 
    "doctor_id INTEGER NOT NULL REFERENCES Doctor(id)," + 
    "patient_id INTEGER NOT NULL REFERENCES Patient(id)," +
    "cost DOUBLE NOT NULL," + 
    "date DATE" + 
    ")";

修正方案二：调整表级外键约束的位置 #

如果坚持用表级约束的写法，只需把所有外键约束移到最后一列定义的后面即可：

String sqlVisit = "CREATE TABLE IF NOT EXISTS Visit (" + 
    "id INTEGER PRIMARY KEY NOT NULL," + 
    "doctor_id INTEGER NOT NULL," + 
    "patient_id INTEGER NOT NULL," +
    "cost DOUBLE NOT NULL," + 
    "date DATE," + // 注意这里要加逗号，因为后面还有约束声明
    "FOREIGN KEY (doctor_id) REFERENCES Doctor(id)," +
    "FOREIGN KEY (patient_id) REFERENCES Patient(id)" +
    ")";

额外重要提醒 #

SQLite默认是禁用外键约束的，哪怕你创建表时正确定义了外键，默认情况下约束也不会生效。所以在执行建表语句前，记得先执行这条语句开启外键支持：

statement.execute("PRAGMA foreign_keys = ON;");

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