---
title: "每个账号只能有一条有效记录：用 SQLite 部分唯一索引保留历史"
date: 2026-11-13
slug: sqlite-partial-unique-index
canonical: https://kernelranch.com/blog/2026/11/sqlite-partial-unique-index
markdown_url: https://kernelranch.com/blog/2026/11/sqlite-partial-unique-index.md
tags: ["sqlite", "database", "migrations"]
---当系统允许一个账号保留多条历史订阅，但只允许一条有效订阅时，可以用 SQLite 部分唯一索引限制有效行。把校验交给数据库后，多个写入入口使用同一个约束，应用只需处理约束错误。

## 1. 验证三种状态转换

保存为 `partial_unique_lab.py`，用 Python 运行。例中 `ended_at IS NULL` 表示订阅有效。

```python
import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as db:
    db.executescript("""
        CREATE TABLE subscription (
            id INTEGER PRIMARY KEY,
            account_id INTEGER NOT NULL,
            ended_at TEXT
        );
        CREATE UNIQUE INDEX one_active_per_account
            ON subscription(account_id) WHERE ended_at IS NULL;
        INSERT INTO subscription VALUES
            (1, 42, '2026-01-01'), (2, 42, '2026-01-01'), (3, 42, NULL);
    """)

    try:
        with db:
            db.execute("INSERT INTO subscription VALUES (4, 42, NULL)")
    except sqlite3.IntegrityError as exc:
        assert exc.sqlite_errorname == "SQLITE_CONSTRAINT_UNIQUE"
        print("second active subscription:", exc.sqlite_errorname)
    else:
        raise AssertionError("duplicate active subscription accepted")

    with db:
        updated = db.execute("""
            UPDATE subscription SET ended_at = '2026-11-13'
            WHERE id = 3 AND account_id = 42 AND ended_at IS NULL
        """)
        if updated.rowcount != 1:
            raise RuntimeError("the expected active subscription changed")
        db.execute("INSERT INTO subscription VALUES (4, 42, NULL)")

    rows = db.execute("""
        SELECT id, ended_at IS NULL FROM subscription
        WHERE account_id = 42 ORDER BY id
    """).fetchall()
    assert rows == [(1, 0), (2, 0), (3, 0), (4, 1)]
    print("rows (id, active):", rows)
```

```text
second active subscription: SQLITE_CONSTRAINT_UNIQUE
rows (id, active): [(1, 0), (2, 0), (3, 0), (4, 1)]
```

## 2. 迁移之前查旧记录

建索引前用以下查询定位已违反新规则的账号。修复规则应来自业务，例如哪条订阅实际仍在生效，再由迁移验证索引能否建成。

```sql
SELECT account_id, count(*) AS active_count
FROM subscription
WHERE ended_at IS NULL
GROUP BY account_id
HAVING count(*) > 1;
```

替换有效订阅时，在同一事务中先结束旧订阅，再写入新订阅。上例用旧行 ID 和状态做条件更新，避免迟到的操作结束另一条有效记录。索引表达的是“至多一条”。如果某些账号必须始终存在一条有效记录，仍需在业务事务中保证创建、替换和删除符合这条约束。

## 小结

把部分唯一索引用在有明确状态条件的唯一约束上。迁移时检查历史数据，状态替换时用同一事务提交两次写入。

## 数据来源与检索时间

| 来源 | URL | 检索日期 | 对应内容 |
|---|---|---|---|
| SQLite Partial Indexes | https://www.sqlite.org/partialindex.html#unique_partial_indexes | 2026-11-13 | 部分唯一索引的作用范围 |
| SQLite Transaction | https://www.sqlite.org/lang_transaction.html | 2026-11-13 | 事务边界与写入事务行为 |

## 核验说明

- Windows、Python 3.11.9、SQLite 3.45.1 验证历史记录共存、重复有效行被拒以及事务内替换。样本使用内存库和单个连接。