如何在SQLite3数据库中为列设置最大整数值限制？

我正在开发一款采用闪卡法学习新单词的Python程序，使用SQLite3数据库存储数据，每张表对应一对语言。需求是：当用户答对单词配对时，对应列的数值加1，但该数值不得超过4。希望直接在SQLite3数据库层面实现这一规则，而非在Python代码中处理，避免增加代码复杂度。目前我用于创建字典表的函数如下：

def create_dictionary ():
    lang1 = input('Insert first language: ')
    lang2 = input('Insert second language: ')
    dict_name = (lang1 + "_" + lang2)
    cur = conn.cursor()
    cur.execute('''CREATE TABLE IF NOT EXISTS {tab}
  (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
  {word1}   TEXT UNIQUE,
  {word2}   TEXT UNIQUE,
  box12   INTEGER,
  box21   INTEGER);'''.format(tab = dict_name, word1 = lang1, word2 = lang2))  
    print("Table created successfully")
    conn.commit()
    cur.close()

要在SQLite层面实现数值不超过4的限制，可通过检查约束和触发器组合完成：

1. 修改表结构，添加检查约束 #

在创建表时，给box12和box21列添加CHECK约束限定取值范围（0-4），同时设置默认值为0，确保初始状态符合要求：

def create_dictionary ():
    lang1 = input('Insert first language: ')
    lang2 = input('Insert second language: ')
    dict_name = f"{lang1}_{lang2}"
    cur = conn.cursor()
    # 创建带检查约束的表
    cur.execute(f'''CREATE TABLE IF NOT EXISTS {dict_name}
  (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
  {lang1}   TEXT UNIQUE,
  {lang2}   TEXT UNIQUE,
  box12   INTEGER DEFAULT 0 CHECK(box12 BETWEEN 0 AND 4),
  box21   INTEGER DEFAULT 0 CHECK(box21 BETWEEN 0 AND 4));''')  
    print("Table created successfully")
    conn.commit()
    cur.close()

2. 创建触发器，实现自动限幅的加1操作 #

仅靠检查约束无法自动处理“加1后不超过4”的逻辑，需要创建触发器，在更新列值时判断是否超出上限，超出则强制设为4。将触发器创建逻辑整合到建表函数中：

def create_dictionary ():
    lang1 = input('Insert first language: ')
    lang2 = input('Insert second language: ')
    dict_name = f"{lang1}_{lang2}"
    cur = conn.cursor()
    # 创建带检查约束的表
    cur.execute(f'''CREATE TABLE IF NOT EXISTS {dict_name}
  (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
  {lang1}   TEXT UNIQUE,
  {lang2}   TEXT UNIQUE,
  box12   INTEGER DEFAULT 0 CHECK(box12 BETWEEN 0 AND 4),
  box21   INTEGER DEFAULT 0 CHECK(box21 BETWEEN 0 AND 4));''')  
    # 创建box12的限幅触发器
    cur.execute(f'''CREATE TRIGGER IF NOT EXISTS increment_box12_{dict_name}
BEFORE UPDATE OF box12 ON {dict_name}
FOR EACH ROW
WHEN NEW.box12 > 4
BEGIN
    SET NEW.box12 = 4;
END;''')
    # 创建box21的限幅触发器
    cur.execute(f'''CREATE TRIGGER IF NOT EXISTS increment_box21_{dict_name}
BEFORE UPDATE OF box21 ON {dict_name}
FOR EACH ROW
WHEN NEW.box21 > 4
BEGIN
    SET NEW.box21 = 4;
END;''')
    print("Table and triggers created successfully")
    conn.commit()
    cur.close()

3. 使用方式 #

后续更新答对计数时，直接执行简单的加1SQL语句即可，无需在Python代码中判断数值：

# 示例：答对从lang1到lang2的单词配对，给box12加1
cur.execute(f"UPDATE {dict_name} SET box12 = box12 + 1 WHERE {lang1} = ?", (target_word,))

如果当前box12已经是4，触发器会自动将新值修正为4，确保不会超出限制。

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