C++调用sqlite3创建带FOREIGN KEY关联表报near 'FOREIGN'语法错误

你遇到的语法报错是因为SQLite的外键约束声明规则和其他常见关系型数据库存在差异：SQLite不支持在列定义中直接追加FOREIGN KEY关键字声明外键，你参考的示例大概率是MySQL、PostgreSQL等数据库的写法，无法兼容SQLite。

有两种写法可以正常创建带外键的表：

方案1：使用列级外键简化写法 #

直接移除FOREIGN KEY关键字即可，SQLite的列级外键不需要显式声明该关键字，改写后的建表语句如下：

sql = "CREATE TABLE IF NOT EXISTS bank_account("
    "customer_id INTEGER REFERENCES customer(id),"
    "account_number     INT  NOT NULL,"
    "balance            INT  NOT NULL,"
    "username           TEXT NOT NULL,"
    "password           TEXT NOT NULL);";

方案2：使用表级外键约束声明 #

把外键约束单独作为表的配置项放在所有列定义之后，写法如下：

sql = "CREATE TABLE IF NOT EXISTS bank_account("
    "customer_id INTEGER NOT NULL,"
    "account_number     INT  NOT NULL,"
    "balance            INT  NOT NULL,"
    "username           TEXT NOT NULL,"
    "password           TEXT NOT NULL,"
    "FOREIGN KEY (customer_id) REFERENCES customer(id));";

SQLite默认关闭外键约束校验，建表完成后如果需要外键关联规则生效，需要在操作数据库前执行一次SQL语句：PRAGMA foreign_keys = ON;，否则插入不存在的customer_id也不会触发关联报错。

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