结构化查询语言/修改表

ALTER TABLE 命令可以修改列定义和表约束，这些修改“动态”进行。这意味着现有的定义可以被扩展、改变或删除，或者现有数据可以被强制转换为不同的类型，或者现有数据可以根据新的定义进行评估。

-- change column definitions
ALTER TABLE <table_name> { ADD | ALTER } [ COLUMN ] <column_name> <column_definition>;
ALTER TABLE <table_name> { DROP        } [ COLUMN ] <column_name>;

-- change table constraints
ALTER TABLE <table_name> { ADD | ALTER } CONSTRAINT <constraint_name> <constraint_definition>;
ALTER TABLE <table_name> { DROP        } CONSTRAINT <constraint_name>;

以下示例基于测试表t1。

CREATE TABLE t1 (
  id         NUMERIC  PRIMARY KEY,
  col_1      CHAR(4)
);

列

ADD COLUMN 和 ALTER COLUMN 语句的语法与创建表页面中显示的类似。

添加一列

可以使用 ADD COLUMN 语句为现有表添加额外的列。在此语句中，可以使用原始 Create Table 语句中的所有选项：数据类型、默认值、NOT NULL、主键、唯一键、外键、检查约束。

-- add a new column with any characteristic
ALTER TABLE t1 ADD COLUMN col_2 VARCHAR(100) CHECK (length(col_2) > 5);  -- Oracle: The key word 'COLUMN' is not allowed.

更改列的特性

使用 ALTER COLUMN 语句可以更改现有列的某些特性

• 数据类型
• 默认值
• 非空约束。

新的定义必须与旧的现有数据兼容。例如，如果您将数据类型从 VARCHAR 更改为 NUMERIC，则此操作只有在可以将所有现有的 VARCHAR 数据强制转换为 NUMERIC 时才会成功 - 将 'xyz' 强制转换为 NUMERIC 会失败。从 NUMERIC 到 VARCHAR 的强制转换将成功，只要 VARCHAR 的宽度足以存储结果即可。

提示：在更改列的特性时，一些实现会忽略 SQL 标准的语法，并使用其他关键字，如 'MODIFY'。

更改数据类型

ALTER TABLE t1 ALTER COLUMN col_1 SET DATA TYPE NUMERIC;

更改默认值

ALTER TABLE t1 ALTER COLUMN col_1 SET DEFAULT 'n/a';

更改非空约束

ALTER TABLE t1 ALTER COLUMN col_1 SET  NOT NULL;
ALTER TABLE t1 ALTER COLUMN col_1 DROP NOT NULL;

删除一列

可以从现有表中删除列。

ALTER TABLE t1 DROP COLUMN col_2;    -- Oracle: The key word 'COLUMN' is mandatory.

提示：作为对 SQL 标准的扩展，一些实现提供了 RENAME 或 SET INVISIBLE 命令。

表约束

可以添加、修改或删除表约束。语法与创建表页面中显示的类似。

添加表约束

ALTER TABLE t1 ADD CONSTRAINT t1_col_1_unique UNIQUE (col_1);

更改表约束

ALTER TABLE t1 ALTER CONSTRAINT t1_col_1_unique UNIQUE (col_1);

提示：在更改表约束时，一些实现会忽略 SQL 标准的语法，并使用其他关键字，如 'MODIFY'。

删除表约束

ALTER TABLE t1 DROP CONSTRAINT t1_col_1_unique;  -- MySQL: Not supported. Use 'DROP FOREIGN KEY' or 'DROP INDEX' instead.

提示：作为对 SQL 标准的扩展，一些实现提供了 ENABLE / DISABLE 命令用于约束。

练习

在 't1' 表中添加一列 'col_3'：numeric 类型，非空。

点击查看解决方案

ALTER TABLE t1 ADD COLUMN col_3 NUMERIC NOT NULL;

从 't1' 表的 'col_3' 列添加一个外键到 'person' 表的 'id' 列。

点击查看解决方案

ALTER TABLE t1 ADD CONSTRAINT t1_col_3_fk FOREIGN KEY (col_3) REFERENCES person (id);