修改表

1. 概述

SQLite 支持 ALTER TABLE 的有限子集。SQLite 中的 ALTER TABLE 命令允许对现有表进行以下更改：可以重命名表；可以重命名列；可以向其中添加列；或者可以从中删除列。

2. ALTER TABLE 重命名

RENAME TO 语法将 table-name 的名称更改为 new-table-name。此命令不能用于在附加的数据库之间移动表，只能用于重命名同一数据库中的表。如果要重命名的表具有触发器或索引，则这些触发器或索引在表重命名后仍将附加到该表。

兼容性说明：在版本 3.25.0（2018-09-15）和 3.26.0（2018-12-01）中增强了 ALTER TABLE 重命名表时的行为，以便将重命名操作转发到引用重命名表的触发器和视图中。这被认为是一个改进。依赖于旧有（并且可以说是有错误的）行为的应用程序可以使用 PRAGMA legacy_alter_table=ON 语句或 SQLITE_DBCONFIG_LEGACY_ALTER_TABLE 配置参数在 sqlite3_db_config() 接口上，使 ALTER TABLE RENAME 的行为与 3.25.0 版本之前一样。

从 3.25.0（2018-09-15）版本开始，触发器主体和视图定义中对表的引用也将被重命名。

在 3.26.0（2018-12-01）版本之前，只有在 PRAGMA foreign_keys=ON 时，才会编辑对重命名表的 FOREIGN KEY 引用，换句话说，如果正在强制执行外键约束。使用 PRAGMA foreign_keys=OFF，当外键引用的表（“父表”）重命名时，不会更改 FOREIGN KEY 约束。从 3.26.0 版本开始，除非启用了 PRAGMA legacy_alter_table=ON 设置，否则在重命名表时始终会转换 FOREIGN KEY 约束。下表总结了差异

PRAGMA foreign_keys PRAGMA legacy_alter_table 更新父表引用 SQLite 版本
关闭关闭否 < 3.26.0
关闭关闭是 >= 3.26.0
开启关闭是所有
关闭开启否所有
开启开启是所有

PRAGMA foreign_keys	PRAGMA legacy_alter_table	更新父表引用	SQLite 版本
关闭	关闭	否	< 3.26.0
关闭	关闭	是	>= 3.26.0
开启	关闭	是	所有
关闭	开启	否	所有
开启	开启	是	所有

3. ALTER TABLE 重命名列

RENAME COLUMN TO 语法将表 table-name 的 column-name 更改为 new-column-name。列名在表定义本身以及所有引用该列的索引、触发器和视图中都会发生更改。如果列名的更改会导致触发器或视图中的语义歧义，则 RENAME COLUMN 会失败并报错，并且不会应用任何更改。

4. ALTER TABLE 添加列

ADD COLUMN 语法用于向现有表添加新列。新列始终附加到现有列列表的末尾。 column-def 规则定义新列的特征。新列可以采用 CREATE TABLE 语句中允许的任何形式，但以下限制除外

列不能具有 PRIMARY KEY 或 UNIQUE 约束。
列不能具有 CURRENT_TIME、CURRENT_DATE、CURRENT_TIMESTAMP 或括号中表达式的默认值。
如果指定了 NOT NULL 约束，则列必须具有非 NULL 的默认值。
如果外键约束已启用并且添加了带有 REFERENCES 子句的列，则该列必须具有 NULL 的默认值。
列不能是 GENERATED ALWAYS ... STORED，但允许使用 VIRTUAL 列。

在添加带有 CHECK 约束的列或具有 NOT NULL 约束的生成列时，会针对表中的所有预先存在的行测试添加的约束，如果任何约束失败，则 ADD COLUMN 会失败。从 SQLite 3.37.0（2021-11-27）版本开始，针对预先存在的行测试添加的约束是一个新的增强功能。

ALTER TABLE 命令通过修改存储在 sqlite_schema 表中的模式的 SQL 文本工作。对于重命名或不带约束的列添加，不会对表内容进行任何更改。因此，此类 ALTER TABLE 命令的执行时间与表中的数据量无关，并且此类命令在具有 1000 万行的表上的运行速度与在具有 1 行的表上的运行速度一样快。在添加具有 CHECK 约束的新列、添加具有 NOT NULL 约束的生成列或删除列时，必须读取（以针对现有行测试新约束）或写入（以删除已删除的列）表中的所有现有数据。在这些情况下，ALTER TABLE 命令花费的时间与要更改的表中的内容量成正比。

在数据库上运行 ADD COLUMN 后，SQLite 3.1.3（2005-02-20）及更早版本将无法读取该数据库。

5. ALTER TABLE 删除列

DROP COLUMN 语法用于从表中删除现有列。DROP COLUMN 命令从表中删除指定的列，并重写其内容以清除与该列关联的数据。仅当列未被模式的其他任何部分引用且不是 PRIMARY KEY 并且没有 UNIQUE 约束时，DROP COLUMN 命令才有效。DROP COLUMN 命令可能失败的原因包括

该列是 PRIMARY KEY 或其一部分。
该列具有 UNIQUE 约束。
该列已建立索引。
该列在部分索引的 WHERE 子句中命名。
该列在与要删除的列无关的表或列 CHECK 约束中命名。
该列用于外键约束中。
该列用于生成列的表达式中。
该列出现在触发器或视图中。

5.1. 工作原理

SQLite 将模式作为纯文本存储在 sqlite_schema 表中。DROP COLUMN 命令（以及所有其他 ALTER TABLE 变体）都会修改该文本，然后尝试重新解析整个模式。只有在修改文本后模式仍然有效的情况下，该命令才会成功。在 DROP COLUMN 命令的情况下，唯一修改的文本是从 CREATE TABLE 语句中删除列定义。如果模式的其他部分中存在该列的任何痕迹，这些痕迹会阻止模式在修改 CREATE TABLE 语句后进行解析，则 DROP COLUMN 命令将失败。

6. 使用 PRAGMA writable_schema=ON 禁用错误检查

如果 ALTER TABLE 遇到任何无法解析的 sqlite_schema 表中的条目，则通常会失败并且不会进行任何更改。例如，如果与名为“tbl1”的表关联的 VIEW 或 TRIGGER 格式错误，则尝试将“tbl1”重命名为“tbl1neo”将失败，因为无法解析关联的视图和触发器。

从 SQLite 3.38.0（2022-02-22）版本开始，可以通过设置“PRAGMA writable_schema=ON;”来禁用此错误检查。当模式可写时，ALTER TABLE 会静默忽略任何无法解析的 sqlite_schema 表的行。

7. 进行其他类型的表模式更改

SQLite 直接支持的唯一模式更改命令是上面显示的“重命名表”、“重命名列”、“添加列”、“删除列”命令。但是，应用程序可以使用一系列简单的操作对表的格式进行其他任意更改。对某个表 X 的模式设计进行任意更改的步骤如下

如果启用了外键约束，请使用 PRAGMA foreign_keys=OFF 禁用它们。
开始事务。
记住与表 X 关联的所有索引、触发器和视图的格式。在下面的步骤 8 中将需要此信息。一种方法是运行如下查询：SELECT type, sql FROM sqlite_schema WHERE tbl_name='X'。
使用 CREATE TABLE 构造一个新的表“new_X”，该表采用所需的表 X 的修订格式。当然，请确保名称“new_X”不会与任何现有的表名冲突。
使用类似以下语句将内容从 X 传输到 new_X：INSERT INTO new_X SELECT ... FROM X。
删除旧表 X：DROP TABLE X。
使用以下命令将 new_X 的名称更改为 X：ALTER TABLE new_X RENAME TO X。
使用 CREATE INDEX、CREATE TRIGGER 和 CREATE VIEW 重建与表 X 关联的索引、触发器和视图。也许可以使用从上面步骤 3 中保存的触发器、索引和视图的旧格式作为指南，并根据需要进行更改以适应更改。
如果任何视图以受模式更改影响的方式引用表 X，则使用 DROP VIEW 删除这些视图，并使用 CREATE VIEW 重新创建它们，并进行必要的更改以适应模式更改。
如果最初启用了外键约束，则运行 PRAGMA foreign_key_check 以验证模式更改是否未破坏任何外键约束。
提交在步骤 2 中开始的事务。
如果最初启用了外键约束，则现在重新启用它们。

注意：请务必精确遵循上述步骤。下面的框总结了修改表定义的两个过程。乍一看，它们似乎都实现了相同的功能。但是，右侧的过程并不总是有效，尤其是在版本 3.25.0 和 3.26.0 添加的增强的重命名表功能的情况下。在右侧的过程中，将表最初重命名为临时名称可能会破坏触发器、视图和外键约束中对该表的引用。左侧的安全过程使用新的临时名称构造修订后的表定义，然后将表重命名为其最终名称，这不会破坏链接。

↑ 正确	↑ 错误
创建新表复制数据删除旧表将新表重命名为旧表	重命名旧表创建新表复制数据删除旧表

上面12步的广义 ALTER TABLE 过程即使模式更改导致表中存储的信息发生变化也能正常工作。因此，上述完整的12步过程适用于删除列、更改列的顺序、添加或删除 UNIQUE 约束或 PRIMARY KEY、添加 CHECK 或 FOREIGN KEY 或 NOT NULL 约束，或更改列的数据类型，例如。但是，对于某些不会以任何方式影响磁盘内容的更改，可以选择使用更简单、更快的过程。以下更简单的过程适用于删除 CHECK 或 FOREIGN KEY 或 NOT NULL 约束，或添加、删除或更改列上的默认值。

开始事务。
运行PRAGMA schema_version以确定当前模式版本号。此编号将在下面的步骤 6 中用到。
使用PRAGMA writable_schema=ON激活模式编辑。
运行一个UPDATE语句来更改sqlite_schema 表中表 X 的定义：UPDATE sqlite_schema SET sql=... WHERE type='table' AND name='X';
注意：如果更改包含语法错误，则像这样对 sqlite_schema 表进行更改将导致数据库损坏且无法读取。建议在使用它之前，先在单独的空白数据库上仔细测试 UPDATE 语句，然后再将其用于包含重要数据的数据库。
如果对表 X 的更改也影响了模式内的其他表或索引或触发器视图，则运行UPDATE语句以修改这些其他表索引和视图。例如，如果列名更改，则必须修改所有引用该列的 FOREIGN KEY 约束、触发器、索引和视图。
注意：再次强调，如果更改包含错误，则像这样对 sqlite_schema 表进行更改将导致数据库损坏且无法读取。在将其用于包含重要数据和/或在运行此过程之前备份重要数据库副本之前，请在单独的测试数据库上仔细测试此整个过程。
使用PRAGMA schema_version=X递增模式版本号，其中 X 比步骤 2 中找到的旧模式版本号大 1。
使用PRAGMA writable_schema=OFF禁用模式编辑。
（可选）运行PRAGMA integrity_check以验证模式更改是否损坏了数据库。
提交在步骤 1 中开始的事务。

如果 SQLite 的某些未来版本添加了新的 ALTER TABLE 功能，那么这些功能很可能将使用上面概述的两个过程之一。

8. 为什么 ALTER TABLE 对 SQLite 来说是一个难题

大多数 SQL 数据库引擎都将模式存储在已解析到各种系统表中。在这些数据库引擎上，ALTER TABLE 仅需对相应的系统表进行修改。

SQLite 与众不同，因为它将模式存储在sqlite_schema表中，作为定义模式的 CREATE 语句的原始文本。因此，ALTER TABLE 需要修改 CREATE 语句的文本。对于某些“创意”模式设计，这样做可能很棘手。

将模式存储为文本的 SQLite 方法对于嵌入式关系数据库具有优势。首先，这意味着模式在数据库文件中占用的空间更少。这一点很重要，因为常见的 SQLite 使用模式是拥有许多小的、独立的数据库文件，而不是将所有内容都放在一个大型的全局数据库文件中，这是客户端/服务器数据库引擎通常采用的方法。由于模式在每个单独的数据库文件中都进行了复制，因此保持模式表示的紧凑性非常重要。

将模式存储为文本而不是解析后的表也为实现提供了灵活性。由于每次打开数据库时都会重新生成模式的内部解析，因此模式的内部表示可以在一个版本到下一个版本之间发生变化。这一点很重要，因为有时新功能需要增强内部模式表示。如果模式表示在数据库文件中公开，则更改内部模式表示将变得更加困难。因此，换句话说，将模式存储为文本有助于保持向后兼容性，并有助于确保旧版数据库文件可以被新版 SQLite 读取和写入。

将模式存储为文本还可以使SQLite 数据库文件格式更容易定义、记录和理解。这有助于使 SQLite 数据库文件成为推荐的数据长期存档存储格式。

将模式存储为文本的缺点是它可能使模式难以修改。出于这个原因，SQLite 中的 ALTER TABLE 支持传统上一直落后于其他将模式存储为更容易修改的解析系统表的 SQL 数据库引擎。

此页面上次修改于 2022-08-10 18:45:48 UTC