SQLite中文网
修改表

一、概述

改变表 stmt：

ALTER TABLE schema-name . table-name RENAME TO new-table-name RENAME COLUMN column-name TO new-column-name ADD COLUMN column-def DROP COLUMN column-name

列定义：

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

2.更改表重命名

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语句或 sqlite3_db_config()接口上的SQLITE_DBCONFIG_LEGACY_ALTER_TABLE配置参数来使 ALTER TABLE RENAME 的行为与 3.25.0 之前的版本一样。

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

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

PRAGMA foreign_keys PRAGMA legacy_alter_table Parent Table references are updated SQLite version
OffOffNo< 3.26.0
OffOffYes>= 3.26.0
OnOffYesall
OffOnNoall
OnOnYesall

3.更改表重命名列

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

4.更改表添加列

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

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

当在生成的列上添加一个带有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.改变表删除列

DROP COLUMN 语法用于从表中删除现有列。DROP COLUMN 命令从表中删除命名列，并重写其内容以清除与该列关联的数据。DROP COLUMN 命令仅在该列未被模式的任何其他部分引用并且不是 PRIMARY KEY 且没有 UNIQUE 约束时才有效。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 表中遇到任何未解析的条目，通常会失败并且不会进行任何更改。例如，如果有一个格式错误的 VIEW 或 TRIGGER 关联了一些名为“tbl1”的表，那么将“tbl1”重命名为“tbl1neo”的尝试将失败，因为无法解析关联的视图和触发器。

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

7.进行其他类型的表架构更改

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

  1. 如果启用了外键约束，请使用PRAGMA foreign_keys=OFF禁用它们。

  2. 开始交易。

  3. 记住与表 X 关联的所有索引、触发器和视图的格式。下面的第 8 步将需要此信息。一种方法是运行如下查询：SELECT type, sql FROM sqlite_schema WHERE tbl_name='X'。

  4. 使用CREATE TABLE构建一个新表“new_X”，它采用表 X 的所需修订格式。当然，请确保名称“new_X”不与任何现有表名冲突。

  5. 使用如下语句将内容从 X 传输到 new_X：INSERT INTO new_X SELECT ... FROM X。

  6. 删除旧表 X： DROP TABLE X。

  7. 使用以下命令将 new_X 的名称更改为 X：ALTER TABLE new_X RENAME TO X。

  8. 使用CREATE INDEX、CREATE TRIGGER和CREATE VIEW 重建与表 X 关联的索引、触发器和视图。也许使用从上面的步骤 3 中保存的触发器、索引和视图的旧格式作为指南，根据需要进行适当的更改改动。

  9. 如果任何视图以受架构更改影响的方式引用表 X，则使用DROP VIEW删除这些视图，并使用CREATE VIEW使用任何必要的更改重新创建它们以适应架构更改。

  10. 如果最初启用了外键约束，则运行PRAGMA foreign_key_check以验证模式更改没有破坏任何外键约束。

  11. 提交在步骤 2 中开始的事务。

  12. 如果最初启用了外键约束，现在重新启用它们。

注意：注意 严格按照上述步骤操作。下面的框总结了修改表定义的两个过程。乍一看，他们似乎都在完成同一件事。但是，右侧的过程并不总是有效，尤其是在版本 3.25.0 和 3.26.0 添加了增强的重命名表功能的情况下。在右侧的过程中，将表初始重命名为临时名称可能会破坏触发器、视图和外键约束中对该表的引用。左边的安全过程使用新的临时名称构造修改后的表定义，然后将表重命名为其最终名称，这不会中断链接。

  1. Create new table
  2. Copy data
  3. Drop old table
  4. Rename new into old
  1. Rename old table
  2. Create new table
  3. Copy data
  4. Drop old table
↑
Correct 		↑
Incorrect

即使模式更改导致存储在表中的信息发生更改，上面的 12 步通用 ALTER TABLE 过程仍然有效。因此，上面完整的 12 步过程适用于删除列、更改列的顺序、添加或删除 UNIQUE 约束或 PRIMARY KEY、添加 CHECK 或 FOREIGN KEY 或 NOT NULL 约束，或更改列的数据类型，例如例子。但是，对于一些不会以任何方式影响磁盘内容的更改，可以选择使用更简单、更快速的过程。以下更简单的过程适用于删除 CHECK 或 FOREIGN KEY 或 NOT NULL 约束，或者添加、删除或更改列的默认值。

  1. 开始交易。

  2. 运行PRAGMA schema_version以确定当前模式版本号。下面的第 6 步将需要此编号。

  3. 使用PRAGMA writable_schema=ON激活模式编辑 。

  4. 运行UPDATE语句以更改sqlite_schema 表中表 X 的定义： UPDATE sqlite_schema SET sql=... WHERE type='table' AND name='X';

    注意： 如果更改包含语法错误，那么像这样更改 sqlite_schema 表将导致数据库损坏且不可读。建议在将 UPDATE 语句用于包含重要数据的数据库之前，先在单独的空白数据库上对其进行仔细测试。

  5. 如果对表 X 的更改也影响其他表或索引或触发器是模式中的视图，则运行UPDATE语句来修改那些其他表的索引和视图。例如，如果列的名称发生更改，则必须修改引用该列的所有 FOREIGN KEY 约束、触发器、索引和视图。

    注意： 再一次，如果更改包含错误，像这样对 sqlite_schema 表进行更改将导致数据库损坏且不可读。在包含重要数据的数据库上使用它之前，请在单独的测试数据库上仔细测试整个过程和/或在运行此过程之前制作重要数据库的备份副本。

  6. 使用 PRAGMA schema_version=X增加架构版本号，其中 X 比上面步骤 2 中找到的旧架构版本号多 1。

  7. 使用PRAGMA writable_schema=OFF禁用模式编辑 。

  8. （可选）运行PRAGMA integrity_check以验证模式更改没有损坏数据库。

  9. 提交在上面的第 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 数据库引擎，这些引擎将其模式存储为更易于修改的已解析系统表。