一、能删的：CHECK

ALTER TABLE ... DROP CONSTRAINT 名字 在沙箱里能用，但只对 CHECK 有效。 删之前先把表上的约束列出来，删之后再列一遍，对比最直观。

ck_price 那一行整条没了，别的列定义一个没动。 这就是「只改一条约束」的正确姿势，不用重建整张表。 MySQL 8.0.19 才加上这个语法，再早的版本只能靠 MODIFY COLUMN 重写整列。 教材用的就是这个语法，说明它假定的是比较新的版本。

二、删不掉的：外键与唯一约束

两种失败要分开读：一种是「不许删」，一种是「找不到这个名字」。

外键不许删，是因为它牵着另一张表；主键、唯一约束同理，背后是索引。 这些都不是「改一句文本」能拿掉的东西。 MySQL 那边可以 DROP FOREIGN KEY 名字，主键也能 DROP PRIMARY KEY。 沙箱不给这条路，只能走下面的重建表。

三、能加的：只有 CHECK

ADD CONSTRAINT 在沙箱里只认 CHECK，加别的直接是语法错误。

注意后两行是「语法错误」，不是「不支持这种约束」——ADD CONSTRAINT 后面只认 CHECK。 想加唯一约束，用第 11 章的 CREATE UNIQUE INDEX，效果一样。 新加的约束会被追加到建表语句末尾，位置和你手写时想的不一样，但语义不变。

四、删不掉怎么办：重建表

标准做法五步：建新表、搬数据、删旧表、改名、补索引和触发器。 中间那三步要包在一个事务里，因为旧表删掉到改名之间有一段「表不存在」的空档。

行数保住了、外键清掉了，但索引也一起清零了。 新表建的时候没写索引，数据搬过来之后就没有索引——这一条最容易漏。 所以重建表之后必须重新建索引，否则查询会突然变慢，而且没人会想到是这次改动引起的。 原来的触发器也一样，DROP TABLE 的时候跟着没了。

五、开关外键的两个坑

第一个坑：沙箱的外键默认是关的，PRAGMA foreign_keys 读到 0。 它是连接级设置，换一个连接进来又回到关的状态。 第二个坑更隐蔽：在事务里开这个开关是空操作。 实测在 BEGIN 之后设 ON，事务里读到 0，提交之后还是 0——它被默默忽略了。

所以开关要放在 BEGIN 之前。 而做批量导入时恰恰是「先 BEGIN 再想关外键」，顺序一反就不生效，导完才发现数据是脏的。

六、查存量脏数据：foreign_key_check

打开外键之前，先扫一遍库里已经存在的脏数据，不然一开就全是报错。 PRAGMA foreign_key_check 干的就是这件事：把「子表里有、父表里没有」的行全列出来。

实测插两条野数据（pid 分别是 42 和 77），它就列出两行，rowid 也对得上。 这一句在两种时候最有用：刚打开外键开关时、刚导入完别人的数据时。 第 13 章到这里结束。这一章要带走三句话： 约束挡在写入那一刻，应用层绕不过；缺省值不等于默认正确，外键默认是关的； 改约束先看能不能删，删不掉就重建表，重建完记得补索引。 13.5 的练习可以挑这三道：给一张表加一条 CHECK 再删掉、把外键改成 CASCADE 走一遍重建表、 打开外键开关后用 foreign_key_check 找出脏数据并清掉。 三道都动手做过，这一章就算真读完了。

七、动手 Demo：删约束、加约束、重建表

这一段把「改约束」的三种情况各走一遍：能删的删掉、删不掉的报错、加不上的换写法。 最后演示重建表的完整流程，以及重建之后丢了什么。

import sqlite3
c = sqlite3.connect(u":memory:", isolation_level=None)
c.executescript(DDL)   # 与上一篇同一段建表语句

def constraints():
    sql = c.execute(u"SELECT sql FROM sqlite_master "
                     u"WHERE name = 'tb_goods'").fetchone()[0]
    return [l.strip() for l in sql.split(u"\n") if u"CONSTRAINT" in l]

# ① 能删的：CHECK
print(constraints())
c.execute(u"ALTER TABLE tb_goods DROP CONSTRAINT ck_price")
print(constraints())

# ② 删不掉的：外键
try:
    c.execute(u"ALTER TABLE tb_goods DROP CONSTRAINT fk_cat")
except sqlite3.OperationalError as e:
    print(e)

# ③ 能加的：只有 CHECK
c.execute(u"ALTER TABLE tb_goods ADD CONSTRAINT ck_name "
          u"CHECK (length(name) > 0)")
try:
    c.execute(u"ALTER TABLE tb_goods ADD CONSTRAINT uq_x UNIQUE (name)")
except sqlite3.OperationalError as e:
    print(e)

# ④ 删不掉就重建：注意索引会丢
c.execute(u"CREATE INDEX i_goods_cat ON tb_goods (cat_id)")
c.execute(u"BEGIN")
c.execute(u"CREATE TABLE tb_goods_new (id INTEGER PRIMARY KEY, "
          u"name TEXT NOT NULL, cat_id INTEGER, price REAL DEFAULT 0)")
c.execute(u"INSERT INTO tb_goods_new (id, name, cat_id, price) "
          u"SELECT id, name, cat_id, price FROM tb_goods")
c.execute(u"DROP TABLE tb_goods")
c.execute(u"ALTER TABLE tb_goods_new RENAME TO tb_goods")
c.execute(u"COMMIT")
print(len(c.execute(u"PRAGMA index_list(tb_goods)").fetchall()))

# ⑤ 开关外键的时机
c.execute(u"BEGIN")
c.execute(u"PRAGMA foreign_keys = ON")
print(c.execute(u"PRAGMA foreign_keys").fetchone()[0])
c.execute(u"COMMIT")
print(c.execute(u"PRAGMA foreign_keys").fetchone()[0])

# ⑥ 存量脏数据
c.execute(u"CREATE TABLE tb_p (id INTEGER PRIMARY KEY, name TEXT)")
c.execute(u"CREATE TABLE tb_c (id INTEGER PRIMARY KEY, pid INTEGER, "
          u"CONSTRAINT fk_p FOREIGN KEY (pid) REFERENCES tb_p (id))")
c.execute(u"INSERT INTO tb_p VALUES (1, '甲')")
c.execute(u"INSERT INTO tb_c VALUES (2, 42), (3, 77)")
print(c.execute(u"PRAGMA foreign_key_check").fetchall())

实跑输出：

--------------------------------------------------------------------
第五十九课：改约束——能删的、删不掉的、和只能重建的
--------------------------------------------------------------------

  **教材 13.3 讲删除和修改约束，用的都是 `ALTER TABLE ... DROP CONSTRAINT`。**
  **这个语法 MySQL 8.0.19 才有，沙箱这一版居然也支持，但只支持一半。**
  **这一课把「哪些能删、哪些删不掉、删不掉怎么办」跑成一张表。**

  一、能删的：CHECK
  --------------------------------------------------------------------
  **先看删之前，这张表上有哪几条约束：**
  CONSTRAINT ck_price CHECK (price >= 0),
  CONSTRAINT fk_cat   FOREIGN KEY (cat_id) REFERENCES tb_cat (id)
  **`DROP CONSTRAINT ck_price` 的结果是：成功。**
  **再看删之后：**
  CONSTRAINT fk_cat   FOREIGN KEY (cat_id) REFERENCES tb_cat (id)
  **`ck_price` 那一行整条没了，`fk_cat` 还在，别的列定义一个没动。**
  **这就是「只改一条约束」的正确姿势，不用重建整张表。**

  二、删不掉的：唯一约束与外键
  --------------------------------------------------------------------

  删谁              结果
  -------------------------------------------------------
  `fk_cat`（外键）  constraint may not be dropped: fk_cat
  一个不存在的名字  no such constraint: nope
  **两行的报错不一样，要分开读：第一行 `constraint may not be dropped` 是「不许删」，**
  **第二行 `no such constraint: nope` 是「找不到这个名字」。**
  **外键不许删，是因为它牵着另一张表；主键、唯一约束同理，背后是索引，不是一句话能拿掉的。**
  **MySQL 那边外键是可以 `DROP FOREIGN KEY 名字` 的，主键也能 `DROP PRIMARY KEY`。**
  **沙箱不给这条路，只能走下一节的重建表。**

  三、能加的：CHECK
  --------------------------------------------------------------------
  **`ADD CONSTRAINT ck_name CHECK (...)` 的结果是：成功。**
  **再看表上的约束，多了一条：**
  CONSTRAINT fk_cat   FOREIGN KEY (cat_id) REFERENCES tb_cat (id)
  CONSTRAINT ck_name CHECK (length(name) > 0))
  **新约束被追加到了建表语句末尾，位置和你想的可能不一样，但语义一样。**

  写法                              结果
  --------------------------------------------------------------
  `ADD CONSTRAINT ... CHECK`        支持
  `ADD CONSTRAINT ... UNIQUE`       near "UNIQUE": syntax error
  `ADD CONSTRAINT ... PRIMARY KEY`  near "PRIMARY": syntax error
  **后两行是语法错误，不是「不支持这种约束」，是「`ADD CONSTRAINT` 后面只认 `CHECK`」。**
  **想加唯一约束得用 `CREATE UNIQUE INDEX`，那是第 11 章的办法。**

  四、删不掉怎么办：重建表
  --------------------------------------------------------------------
  **标准做法是五步：建新表、搬数据、删旧表、改名、补索引和触发器。**
  **搬数据和改名之间，旧表已经没了，所以整段要包在一个事务里。**
  **重建之前先给这张表补一个索引，这样才看得出重建会丢掉什么：**
  **当前有 1 个索引。**

  检查项  重建之后
  ----------------
  行数    2
  外键    0
  索引    0
  **行数保住了，外键清零（这就是我们要的），但索引也一起清零了。**
  **新表建的时候没写索引，数据搬过来之后就没有索引——这一条最容易漏。**
  **所以重建表之后必须重新建索引，否则查询会突然变慢，而且没人会想到是这次改动引起的。**

  五、开关外键的两个坑
  --------------------------------------------------------------------
  **第一个坑：沙箱的外键默认就是关的，`PRAGMA foreign_keys` 给 0。**
  **换一个连接进来，你得自己打开它，不然外键形同虚设。**

  什么时候开的  `PRAGMA foreign_keys` 读到
  ----------------------------------------
  事务里        0
  事务外        0
  **第二个坑更隐蔽：在事务里开这个开关是空操作。**
  **事务里读还是 0，提交之后也没变——它被默默忽略了。**
  **所以开关要放在 `BEGIN` 之前，而做批量导入时恰恰是「先 `BEGIN` 再想关外键」，**
  **顺序一反就不生效，导完才发现数据是脏的。**

  六、查存量脏数据：foreign_key_check
  --------------------------------------------------------------------

  表    rowid  父表  第几个外键
  -----------------------------
  tb_c  2      tb_p  0
  tb_c  3      tb_p  0
  **`PRAGMA foreign_key_check` 扫全库，把「子表里有、父表里没有」的行全列出来。**
  **第一列是表名，第二列是那一行的 rowid，第三列是它该在的父表。**
  **这一句在两种时候最有用：刚打开外键开关时、刚导入完别人的数据时。**
  **先把脏数据清干净再打开强制，不然一开就全是报错。**
  **第 13 章到这里结束。这一章要带走的是三句话：**
  **约束挡在写入那一刻，应用层绕不过；缺省值不等于默认正确，外键默认是关的；**
  **改约束先看能不能删，删不掉就重建表，重建完记得补索引。**

第一组两次打印只差一行：ck_price 那条消失了，fk_cat 还在。 这是「只改一条约束」的正规姿势，数据一行没动。 第二组报 constraint may not be dropped: fk_cat，第三组后半报 near "UNIQUE"。 一个是不许删，一个是语法不认，别混成一类。 第四组先建了一个索引，重建之后打印出 0——索引跟着旧表一起没了。 这个 0 是整段最值钱的一个数：重建表之后必须回头补索引。 第五组两个数都是 0，说明在事务里设 PRAGMA foreign_keys 根本不生效。 开关要放在 BEGIN 之前，这个顺序错了不会报错，只会默默失效。 第六组两行输出就是脏数据清单，rowid 2 和 3 都对得上。 打开强制之前先跑这一句，把脏数据清掉，不然一开全是报错。

30 秒实验

1. 把重建表的顺序改成「先删旧表再建新表」，看事务提交后会怎样。
2. 重建表时不搬 id，让引擎自动编号，看 rowid 有没有变。
3. 把 PRAGMA foreign_keys = ON 从 BEGIN 里挪到外面，再看读到几。

常见误区

1. 以为 DROP CONSTRAINT 什么都能删，遇到外键才发现只支持 CHECK。
2. 重建表之后忘了补索引，查询突然变慢还找不到原因。
3. 在 BEGIN 之后才关外键开关，导完数据才发现约束一直开着或一直关着。

第 13 章到这里结束。下一篇进第 14 章：存储过程和存储函数，第一次把逻辑写进数据库。

• ☐ （本篇完，下一篇 14.1 创建存储过程和存储函数）

相关笔记

• 定义完整性约束
• 命名完整性约束
• 删除索引
• 🛢️ MySQL从入门到精通 · 索引