ipython-sql中数据类型约束不生效的原因及解决方法

问题原因 #

SQLite采用动态类型系统，而非传统关系型数据库的强类型约束。它的列类型定义本质是"类型亲和性"（Type Affinity），仅表示SQLite会优先尝试将插入值转换为对应类型，而非强制限制只能存储该类型数据。

当你向NUMERIC类型列插入无法转换为数值的字符串时，SQLite不会抛出类型不匹配错误，而是直接存储原始字符串——这是SQLite默认的兼容性设计，为了保持灵活性。

解决方法 #

方法1：启用严格模式（STRICT TABLE） #

SQLite 3.37.0及以上版本支持严格模式，创建表时添加STRICT关键字，可强制列的类型约束，不符合要求的插入操作会直接报错。

示例代码：

%%sql sqlite://
CREATE TABLE students(
    st_id INTEGER PRIMARY KEY AUTOINCREMENT,
    fname VARCHAR(15) NOT NULL,
    lname VARCHAR(15) NOT NULL,
    st_gr NUMERIC
) STRICT;

此时插入非数值数据，会触发CHECK constraint failed: students错误，阻止非法数据写入。

方法2：添加CHECK约束 #

若你的SQLite版本低于3.37.0，可手动添加CHECK约束验证数据类型，比如限制st_gr只能是整数或实数类型：

示例代码：

%%sql sqlite://
CREATE TABLE students(
    st_id INTEGER PRIMARY KEY AUTOINCREMENT,
    fname VARCHAR(15) NOT NULL,
    lname VARCHAR(15) NOT NULL,
    st_gr NUMERIC CHECK (typeof(st_gr) IN ('integer', 'real'))
);

插入非数值数据时，CHECK约束会触发报错，拒绝写入。

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