After a SQLite rebuild, test the behavior
SQLite 表重建后，验收行为
Rows can survive while constraints, triggers or uniqueness disappear.
行还在，不代表约束、触发器与唯一性还在。
Synthetic · Offline · Standard library / 合成 · 离线 · 标准库
- Seed rows / 原始行
- 3
- Behaviors / 行为
- 5
- Broken controls / 错误对照
- 5
- Corrected case / 修正版
- 1
10,191 bytes · 13 files · SQL, Python runner, tests and recorded output
含 SQL、Python 程序、测试与实测结果，不含数据库。
Archive SHA-256 / 压缩包校验值
69a2e213144b3c71ac63b365c659509bb4ff1e915139abd61e731c73e25fcc66A migration can finish and still lose a rule
This fixed example rebuilds a tiny membership table, adds a note column and checks the result. It creates only its own temporary databases. No existing database path, SQL input, upload, dependency installation or network access is accepted. Nothing executes on this page.
Run and see the difference
Extract the archive, read README.md and run these commands inside its folder. Python 3.10+ with sqlite3 is required. Actual tested versions: Python 3.12.14, SQLite 3.53.1 on 2026-10-07. Every run prints its own versions.
python3 run.py
python3 run.py --case good
python3 run.py --case bad-trigger
python3 test.pyThe first command succeeds only when the corrected case passes and all five broken cases fail exactly as expected: 25 passing + 5 expected failing checks. In single-case mode, good exits 0 and each bad-* exits 1. An all-controls success never certifies the broken migrations.
Five checks with independent negative controls
- Keep the rows. Compare IDs and exact values, including an inactive duplicate email, Unicode and the new empty notes.
bad-rowdrops one row. - Reject invalid writes. Negative credits, active=2 and NULL email must fail without changing the row.
bad-checkomits the credits CHECK. - Keep the audit trigger. Copying produces no event; a credits change produces exactly one. Repeating the same value produces none.
bad-triggeromits the trigger. - Keep partial uniqueness. Reject duplicate active email and conflicting activation, allow inactive duplicates and a valid handover.
bad-indexomits the partial unique index. - Undo a failed rebuild. Inject an exception after DROP, before rename; compare original schema, rows and audit after rollback.
bad-transactionomits the transaction.
Each faulty case passes the other four checks. Separate savepoints keep behavior probes from affecting one another; rollback gets a separate database. Tests independently state the expected matrix and exact corrected rows.
Use the lesson, respect the boundary
SQLite can add this simple column directly. We deliberately rebuild to illustrate behavior acceptance for changes that require it. The corrected SQL follows create, copy, drop, rename, then recreates the index and trigger inside a transaction.
The official full procedure also handles foreign-key settings/checks and affected views. This fixture has neither. It is not a general migration tool, production repair or proof of schema-wide equivalence. It does not test concurrency, crash durability, ORM reflection or application rules. Do not run its SQL against a real database.
Alembic 1.20 documentation says unnamed CHECK constraints still need explicit handling during batch recreation, and now warns about omitted reflected unnamed CHECKs when no CheckConstraint is supplied in table_args. Named CHECKs have participated since 1.7. This recipe does not run Alembic or claim current versions always omit checks silently.
迁移成功结束，规则仍可能丢失
这个固定合成示例重建一张小型会员表，增加 note 列，再检查行为。程序只创建自己的临时数据库，不接受已有数据库路径、SQL 输入或上传，不安装依赖、不联网。网页本身不执行程序。
运行，观察好坏差异
解压后先读 README.md，在包内目录执行以下命令。需要 Python 3.10+ 及 sqlite3。2026-10-07 实测 Python 3.12.14、SQLite 3.53.1；每次运行都会输出当前实际版本。
python3 run.py
python3 run.py --case good
python3 run.py --case bad-trigger
python3 test.py第一条命令只有在修正版通过、五个错误版都恰好触发预期失败时才成功，共 25 项通过、5 项预期失败。单独运行时 good 退出 0，各 bad-* 退出 1。全部对照成功不表示错误迁移安全。
五种行为，各有独立错误对照
- 保留行。 精确核对 ID 与值，含未启用的重复邮箱、Unicode 和新列空字符串。
bad-row漏复制一行。 - 拒绝非法写入。 负积分、active=2、NULL 邮箱（不含空字符串）都必须失败，原行不变。
bad-check漏掉积分 CHECK。 - 保留审计触发器。 复制不产生日志；积分变化只记一条，重复写相同值不多记。
bad-trigger不重建触发器。 - 保留局部唯一性。 拒绝重复启用与冲突激活，允许未启用的重复行及正确的启用交接。
bad-index不重建局部唯一索引。 - 失败时回滚。 在 DROP 后、重命名前人为中断，回滚后精确核对原结构、行与审计表。
bad-transaction没有包围事务。
每个错误版的其他四项仍通过。行为探测用各自的保存点清除更改，回滚检查另建数据库；测试独立写明预期矩阵，并精确核对修正版的行值。
理解方法与适用边界
SQLite 可直接添加这个简单列。这里特意重建，演示确实需要重建时如何验收行为。修正版依次新建、复制、删除旧表、重命名，再在同一事务内重建索引与触发器。
官方完整流程还处理外键设置、检查及受影响视图，本示例没有这些对象。它不是通用迁移工具、生产修复流程或全库等价证明；不测试并发、崩溃持久性、ORM 反射或应用规则。不要把这些 SQL 用于真实数据库。
Alembic 1.20 文档说明：批量重建仍需显式处理未命名 CHECK；当反射出的未命名 CHECK 被略过且 table_args 未提供 CheckConstraint 时，新版会警告。命名 CHECK 自 1.7 起参与批量重建。本包不运行 Alembic，也不声称新版仍一律静默遗漏。
Sources / 来源
Official documentation checked on 2026-10-07. The SQL and example records are original synthetic material, available under the included MIT license.
2026-10-07 核对官方文档。SQL 与记录均为原创合成材料，按包内 MIT 许可证提供。