南大通用GBase8s之ALTER TABLE ADD/DROP Column语句介绍

本文将详细介绍使用ALTER TABLE ADD/DROP Column语句向表添加/删除列，这两条语句的使用方法、注意事项以及一些实用的示例，帮助开发者更好地掌握这一功能，提升数据库管理效率。

一、ALTER TABLE ADD Column语句

语法图如下：


 

说明和限制：
⦁    不能向包含数据的表添加顺序列（SERIAL、BIGSERIAL或SERIAL8）。
⦁    不能添加超过最大行大小40M字节的列。

示例1：（版本：GBase8sV8.8_TL_3.6.1_2_dd376f，模式：oracle）

create database test with log;
create table testal01(id int,name varchar(10));
--添加列成功
alter table testal01 add num serial;
info columns for testal01;
insert into testal01(id,name) values(1001,'sam');
--查看数据如下
> select * from testal01;
        ID NAME               NUM
      1001 sam                  1
1 row(s) retrieved.

示例2：

create table testal02(id int,name varchar(10));
insert into testal02(id,name) values(1001,'sam');
--不能向包含数据的表添加顺序列
> alter table testal02 add num serial;
 287: Cannot add serial column (num) to table.
Error in line 1
Near character position 34
> info columns for testal02;
Column name          Type                                    Nulls
id                   integer                                 yes
name                 varchar(10,0)                           yes

BEFORE子句

通过BEFORE子句可以指定在表结构中增加新列的位置。如果不包含BEFORE子句，则缺省情况下数据库服务器将新的列添加到当前表结构的最后一列的末尾。
示例：

CREATE TABLE items (
item_id INT,
item_name VARCHAR(255),
price_per_unit DECIMAL(10,2),
total_price DECIMAL(10,2) NOT NULL
);
info columns for items;
--BEFORE选项指定在total_price列之前添加item_weight列
> ALTER TABLE items ADD (item_weight DECIMAL(6,2) NOT NULL BEFORE total_price);
Table altered.
--查看列如下
> info columns for items;
Column name          Type                                    Nulls
item_id              integer                                 yes
item_name            varchar(255,0)                          yes
price_per_unit       decimal(10,2)                           yes
item_weight          decimal(6,2)                            no
total_price          decimal(10,2)                           no

DEFAULT子句

通过DEFAULT子句可以指定新增列的默认值。语法图如下：

说明和限制：

• 支持将NULL作为缺省值；如果将NULL指定为列的缺省值，则不能设置NOT NULL约束；
• 支持将文字值，即字母或数字字符组成的字符串，作为缺省值。例如，default 3.14、 default 'abc_123'；
• 支持语法格式default(literal)，与default literal功能同义。例如，default('123ab')；
• 支持使用常量表达式作为缺省值。例如，default today；
• 支持使用函数表达式作为缺省值。例如，default length('abcdg')；
• 没有规定其他值时，插入一条新数据将DEFAULT值添加到该列中。

示例1： 

create table testal03(
    id int,
    name varchar(30)
);
alter table testal03 add c1 varchar(10) default null;
alter table testal03 add c2 decimal(5, 2) default 3.14;
alter table testal03 add c3 char (10) default 'abc_123';
alter table testal03 add c4 varchar2(10,4) default ('123ab');
alter table testal03 add c5 nvarchar (30) default current_user;
alter table testal03 add c6 date default today;
alter table testal03 add c7 datetime year to second default current;
alter table testal03 add c8 timestamp(3) with time zone default '2024-12-30 13:58:00 +8:00';
alter table testal03 add c9 int default length('abcdg');
alter table testal03 add c10 varchar(20) default hex(128);
--插入数据
insert into testal03 (id,name) values(1001,'sam');
--查看表数据如下
> select * from testal03;
ID    1001
NAME  sam
C1
C2    3.14
C3    abc_123
C4    123ab
C5    gbasedbt
C6    2024-12-30
C7    2024-12-30 14:06:56
C8    2024-12-30 13:58:00.000 +08:00
C9    5
C10   0x00000080
1 row(s) retrieved.

⦁    不能指定顺序列（SERIAL、BIGSERIAL或SERIAL8）的缺省值。
⦁    对于DISTINCT或OPAQUE数据类型的列，不能指定常量表达式（例如：CURRENT、SYSDATE、DBSERVERNAME、SITENAME、TODAY、USER或CURRENT_USER）作为缺省值。

示例2： 

create table testal04(
    id int,
    name varchar(30)
);
--不能指定顺序列的缺省值，以下报错593: Cannot specify default value for SERIAL column.
alter table testal04 add c1 serial default 1001;
alter table testal04 add c2 bigserial default 1001;
alter table testal04 add c3 serial8 default 1001;
--对于DISTINCT或OPAQUE数据类型的列，不能指定常量表达式
--以下报错591: Invalid default value for column/variable (c5).
alter table testal04 add c5 lvarchar default current_user;

约束子句

      通过约束子句可以指定新增列的约束。

说明和限制：

• 如果表包含数据，则不能在新列上指定主键或唯一约束。然而在唯一约束的情况下，表可以包含单行的数据。如果希望添加带有主键约束的列，则发出ALTER TABLE语句时表必须为空。
• 不能在BYTE或TEXT列上指定唯一约束或引用约束。BYTE或TEXT列上的检查约束可只能用于检查IS NULL、IS NOT NULL或LENGTH。
• 如果在同一列上指定NOT NULL约束和NULL约束，则该语句失败。
• 不能在数据类型是LIST、MULTISET、SET或IDSSECURITYLABEL的列上定义NULL约束。

示例1： 

create table testal05(
    id serial,
    createdate date
);
alter table testal05 add customer_id integer constraint pk_cid primary key;
alter table testal05 add cname varchar(30) constraint nn_cn not null;
alter table testal05 add phone char(11) constraint un_ph unique;
alter table testal05 add inivalue decimal(6,2) constraint ck_ini check(inivalue >0 and inivalue <9999);
create table testal06(
    id serial,
    createdate date
);
alter table testal06 add customer_id integer references testal05(customer_id);

示例2： 

create table testal07 (
    id serial,
    createdate date
);
--在空表上添加主键约束，成功
> alter table testal07 add customer_id int primary key;
Table altered.

示例3： 

create table testal08 (
    id serial,
    createdate date
);
--插入单行数据
insert into testal08 (createdate) values (today);
--查看数据
> select * from testal08;
        ID CREATEDATE PHONE
         1 2024-12-30
1 row(s) retrieved.
--尝试添加主键约束，失败
> alter table testal08 add customer_id int primary key;
 269: Cannot add column (customer_id) that does not accept nulls.
Error in line 1
Near character position 51
--尝试添加唯一约束，成功
> alter table testal08 add phone char(11) unique;
Table altered.
--插入第二行数据
> insert into testal08 (createdate,phone) values ('2024-12-25', '13212345678');
1 row(s) inserted.
> select * from testal08;
        ID CREATEDATE PHONE
         1 2024-12-30
         3 2024-12-25 13212345678
2 row(s) retrieved.
--尝试添加唯一约束，失败
> alter table testal08 add statenum char(5) unique;
 371: Cannot create unique index on column with duplicate data.
 100: ISAM error:  duplicate value for a record with unique key.
Error in line 1
Near character position 47

ORACLE模式下添加多列语法

ALTER TABLE支持添加多列的语法，即ADD新列, ADD新列，该功能仅在GBase8s的ORACLE模式下支持。语法图如下：

说明及限制：

• 新添加的列不能和表中已有的列名重复；
• 添加多个列时，添加操作从左到右顺序执行；
• 添加多列语法支持使用原生ADD()语法，添加列使用逗号来分隔。例如，alter table tab1 add (c1 int,c2 int),add (c3 int,c4 int); 。
• 添加列大小不能超过行的最大大小40M字节。

示例： 

create table testal09 (
    id serial,
    createdate date
);
--以下三种写法均执行成功
> alter table testal09 add name varchar2(50) before createdate,
             add phone char(11), 
             add email char(20) not null;
Table altered.
> alter table testal09 add compny varchar(100) add address varchar(100);
Table altered.
> alter table testal09 add (order_num int), add (price decimal(10,2));
Table altered.
--查看列
> info columns for testal09;
Column name          Type                                    Nulls
ID                   SERIAL                                  no
NAME                 VARCHAR2(50,0)                          yes
CREATEDATE           DATE                                    yes
PHONE                CHAR(11)                                yes
EMAIL                CHAR(20)                                no
COMPNY               VARCHAR(100,0)                          yes
ADDRESS              VARCHAR(100,0)                          yes
ORDER_NUM            INTEGER                                 yes
PRICE                DECIMAL(10,2)                           yes

二、ALTER TABLE DROP Column语句

使用DROP Column子句删除表结构中的一列或多列。
在Oracle模式下，单列删除时，被删除列前支持添加COLUMN关键字。
说明和限制：

•  被删除列不能是表的最后一列，至少一列必须保留于表中。
• 不能删除是分片存储策略的分片键一部分的列。

示例： 

create table testal10(
item_id integer,
item_name varchar(255),
price_per_unit decimal(10,2),
item_weight decimal(6,2) not null,
total_price decimal(10,2) not null
);
--以下三种写法均执行成功
> alter table testal10 drop item_id;
Table altered.
> alter table testal10 drop (item_name);
Table altered.
> alter table testal10 drop column price_per_unit;
Table altered.
--查看列
> info columns for testal10;
Column name          Type                                    Nulls
ITEM_WEIGHT          DECIMAL(6,2)                            no
TOTAL_PRICE          DECIMAL(10,2)                           no

删除一列如何影响约束

当删除列时，该列上的所有约束也被删除：、

• 所有单列约束被删除。
• 所有引用该列的引用约束被删除。
• 所有引用该列的检查约束被删除。
• 如果列是多列主键约束或唯一约束的一部分，则多列上放置的约束也被删除。此操作接下来触发引用多列的所有引用约束的删除。
• 由于当删除列时任何与列相关联的约束被删除，因此当使用此子句时其它表的结构也可能改变。例如，如果被删除的列是在其它表中被引用的唯一键或者主键，则那些引用约束也被删除。因此那些其它表的结构也发生改变。

希望本文能够帮助您更好地理解和使用 GBase 8s 的   ALTER TABLE ADD/DROP COLUMN 语句。通过本文的介绍，相信您已经掌握了如何使用这些语句，以及在使用过程中需要注意的事项。如有更多技术分享和交流需求，欢迎关注我们的社区，获取更多实用内容！