---
title: "更改表 | OceanBase 文档中心"
description: 更改表 表创建成功后，您可以使用 ALTER TABLE 语句对表进行修改。 增加、修改和删除列 OceanBase 数据库支持新增列、修改列及其属性、删除列等。 新增列，支持在表中新增列，但不支持增加主键列。示例如下： obclient> DESCRIBE test; +-------+--------------…
image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 3.2.3 企业版 LTS

# 更改表

更新时间：2025-01-17 15:21:21

表创建成功后，您可以使用`ALTER TABLE`语句对表进行修改。

## 增加、修改和删除列

OceanBase 数据库支持新增列、修改列及其属性、删除列等。

- 新增列，支持在表中新增列，但不支持增加主键列。示例如下：

  ```sql
  obclient> DESCRIBE test;
  +-------+--------------+------+-----+---------+-------+
  | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
  +-------+--------------+------+-----+---------+-------+
  | C1    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
  | C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
  +-------+--------------+------+-----+---------+-------+
  2 rows in set (0.01 sec)

  obclient> ALTER TABLE test ADD c3 int;
  Query OK, 0 rows affected (0.03 sec)

  obclient> DESCRIBE test;
  +-------+--------------+------+-----+---------+-------+
  | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
  +-------+--------------+------+-----+---------+-------+
  | C1    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
  | C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL |
  | C3    | NUMBER(38)   | YES  | NULL | NULL    | NULL |
  +-------+--------------+------+-----+---------+-------+
  3 rows in set (0.00 sec)

  ```
 - 修改列属性，列类型。示例如下：

  > **说明**
  >
  >  
  >
  > 修改列类型时，仅支持增加特定字符数据类型（VARCHAR、VARBINARY、CHAR 等）的长度。

  obclient> ALTER TABLE test MODIFY (c2 VARCHAR2(70));
  Query OK, 0 rows affected (0.02 sec)

  obclient> DESCRIBE test;
  +-------+--------------+------+-----+---------+-------+
  | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
  +-------+--------------+------+-----+---------+-------+
  | C1    | NUMBER(38)   | YES  | NULL | NULL    | NULL |
  | C2    | VARCHAR2(70) | YES  | NULL | NULL    | NULL |
  +-------+--------------+------+-----+---------+-------+
  2 rows in set (0.01 sec)

  ```
 - 修改列名。示例如下：

  ```sql
  obclient> ALTER TABLE test RENAME COLUMN c1 TO c;
  Query OK, 0 rows affected (0.02 sec)

  obclient> DESCRIBE test;
  +-------+--------------+------+-----+---------+-------+
  | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
  +-------+--------------+------+-----+---------+-------+
  | C     | NUMBER(38)   | YES  | NULL | NULL    | NULL  |
  | C2    | VARCHAR2(70) | YES  | NULL | NULL    | NULL  |
  +-------+--------------+------+-----+---------+-------+
  2 rows in set (0.00 sec)

  ```
 - 修改列的默认值。示例如下：

  ```sql
  obclient> ALTER TABLE test MODIFY c DEFAULT 1;
  Query OK, 0 rows affected (0.02 sec)

  obclient> DESCRIBE test;
  +-------+--------------+------+-----+---------+-------+
  | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
  +-------+--------------+------+-----+---------+-------+
  | C     | NUMBER(38)   | YES  | NULL | 1       | NULL  |
  | C2    | VARCHAR2(70) | YES  | NULL | NULL    | NULL  |
  +-------+--------------+------+-----+---------+-------+
  2 rows in set (0.00 sec)

  ```
 - 修改列的非空约束。示例如下：

  ```unknow
  obclient> DESCRIBE test;
  +-------+--------------+------+-----+---------+-------+
  | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
  +-------+--------------+------+-----+---------+-------+
  | C     | NUMBER(38)   | YES  | NULL | 1       | NULL  |
  | C2    | VARCHAR2(70) | YES  | NULL | NULL    | NULL  |
  +-------+--------------+------+-----+---------+-------+
  2 rows in set (0.00 sec)

  obclient> ALTER TABLE test MODIFY (c2 NOT NULL);
  Query OK, 0 rows affected (0.05 sec)

  obclient> DESCRIBE test;
  +-------+--------------+------+-----+---------+-------+
  | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
  +-------+--------------+------+-----+---------+-------+
  | C     | NUMBER(38)   | YES  | NULL | 1       | NULL  |
  | C2    | VARCHAR2(70) | NO   | NULL | NULL    | NULL  |
  +-------+--------------+------+-----+---------+-------+
  2 rows in set (0.00 sec)

  ```
 - 删除列，支持删除表中的列，但不允许删除主键列或包含索引的列。示例如下：

  obclient> ALTER TABLE test DROP COLUMN c2;
  Query OK, 0 rows affected (0.04 sec)

  obclient> DESCRIBE test;
  +-------+--------------+------+-----+---------+-------+
  | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
  +-------+--------------+------+-----+---------+-------+
  | C1    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
  +-------+--------------+------+-----+---------+-------+
  1 rows in set (0.00 sec)

  ```

## 修改索引

OceanBase 数据库支持为已存在的表增加唯一索引。示例如下：

```sql
obclient> CREATE TABLE test (c1 int PRIMARY KEY, c2 VARCHAR(50));
Query OK, 0 rows affected (0.04 sec)

obclient> ALTER TABLE test ADD UNIQUE(c2);

```

## 重命名表

表创建成功后，您可以更改表名。

OceanBase 数据库支持重命名表。示例如下：

```sql
obclient> ALTER TABLE test RENAME TO t1;

```

或

```sql
obclient> RENAME test TO t1;

```

## 更改表的外键

- OceanBase 数据库支持添加表的外键。示例如下：

  ```sql
  obclient> ALTER TABLE test  ADD CONSTRAINT fk_name FOREIGN KEY (gid) REFERENCES test1(id);
  Query OK, 0 rows affected (0.06 sec)

  ```
 - ceanBase 数据库支持删除表的外键。示例如下：

  ```sql
  obclient> ALTER TABLE test DROP CONSTRAINT fk_name;

  ```

## 更改表的副本数

设置表的副本数为 `2`。

示例如下：

```sql
obclient> ALTER TABLE test SET REPLICA_NUM=2;
Query OK, 0 rows affected (0.02 sec)

```

## 更改表的表组

更改表的表组相关操作请参考 [表组管理命令](https://www.oceanbase.com/docs/enterprise-oceanbase-database-cn-10000000000355487)。

 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线