SQLite里的SQL SQL As Understood By SQLite

[Top]

更新表
ALTER TABLE

alter-table-stmt: 隐藏

column-def: 显示

column-constraint: 显示

conflict-clause: 显示

expr: 显示

raise-function: 显示

select-stmt: 显示

common-table-expression: 显示

compound-operator: 显示

join-clause: 显示

join-constraint: 显示

join-operator: 显示

ordering-term: 显示

result-column: 显示

table-or-subquery: 显示

foreign-key-clause: 显示

literal-value: 显示

signed-number: 显示

type-name: 显示

signed-number: 显示

SQLite只支持有限的ALTER TABLE操作。在SQLite中ALTER TABLE命令允许用户重命名表名或者在已有表中新增一列。
SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table.

RENAME TO语法可以将 [database-name.]table-name指定的表重命名为new-table-name。这个命令不能用来在附加库间移动表，只能在同一个库内重命名表名。
The RENAME TO syntax is used to rename the table identified by [database-name.]table-name to new-table-name. This command cannot be used to move a table between attached databases, only to rename a table within the same database.

如果重名名的表含有触发器和索引，那么在重名名之后，这些依然指向这张表。然而，如果有一些视图定义或者触发器执行的语句引用了重命名的表，那么这些引用不会自动的修改成新表名。如果需求修改，那么只能删除这些触发器和视图定义，然后重新手工使用新表名创建它们。
If the table being renamed has triggers or indices, then these remain attached to the table after it has been renamed. However, if there are any view definitions, or statements executed by triggers that refer to the table being renamed, these are not automatically modified to use the new table name. If this is required, the triggers or view definitions must be dropped and recreated to use the new table name by hand.

如果重命名的表还有有效的外键约束，那么任何表（包括重命名的表和其他表）上的REFERENCES 子句中引用的旧表名都会被修改为新表名。
If foreign key constraints are enabled when a table is renamed, then any REFERENCES clauses in any table (either the table being renamed or some other table) that refer to the table being renamed are modified to refer to the renamed table by its new name.

ADD COLUMN语法用于在一个已有的表上添加一个新列。新列会被追加到已有列列表的末尾。column-def规则定义新列的特征。新列可以携带任何CREATE TABLE命令中允许的参数，不过有一下限制：
The ADD COLUMN syntax is used to add a new column to an existing table. The new column is always appended to the end of the list of existing columns. The column-def rule defines the characteristics of the new column. The new column may take any of the forms permissible in a CREATE TABLE statement, with the following restrictions:

• 新列不能使用PRIMARY KEY和UNIQUE约束。
  The column may not have a PRIMARY KEY or UNIQUE constraint.
• 新列不能将默认值设置为CURRENT_TIME、 CURRENT_DATE、 CURRENT_TIMESTAMP和包在圆括号内的表达式。
  The column may not have a default value of CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, or an expression in parentheses.
• 如果添加了NOT NULL约束，则必须指定一个非NULL的默认值。
  If a NOT NULL constraint is specified, then the column must have a default value other than NULL.
• 如果表中包含有效的外键约束，并且存在包含REFERENCES 子句的列，那么新列必须将默认值设置为NULL。
  If foreign key constraints are enabled and a column with a REFERENCES clause is added, the column must have a default value of NULL.

在添加一个CHECK 约束时同样需要注意，CHECK约束不会对已有的行做违规测试。这会导致表中包含违反CHECK约束的数据。SQLite以后的版本可能会修改成在添加CHECK约束时做验证。
Note also that when adding a CHECK constraint, the CHECK constraint is not tested against preexisting rows of the table. This can result in a table that contains data that is in violation of the CHECK constraint. Future versions of SQLite might change to validate CHECK constraints as they are added.

ALTER TABLE命令的执行时间依赖于表中数据的总量。ALTER TABLE命令可以在一个一千万行记录的表上执行的像只有一条记录一样快。
The execution time of the ALTER TABLE command is independent of the amount of data in the table. The ALTER TABLE command runs as quickly on a table with 10 million rows as it does on a table with 1 row.

在数据库上运行ADD COLUMN之后，3.1.3版及之前的版本将不能读取该数据库了。
After ADD COLUMN has been run on a database, that database will not be readable by SQLite version 3.1.3 and earlier.