南大通用GBase 8a增加列时报Column cannot be null或 doesn't have a default value错误

GBase 8a支持增加新的列,当新增列指定了not null不许为null时,则必须指定default默认值,否则就会报Column cannot be null或 doesn't have a default value错误。

报错样例

gbase> desc t1;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | bigint(20)    | YES  |     | NULL    |       |
| num   | decimal(20,0) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.07)

gbase> alter table t1 add address varchar(100) not null;
ERROR 1702 (HY000): gcluster table error: Column 'address' cannot be null.

解决方案

既然新增的列不许为null,那就用default指定个默认值就行了。

gbase> alter table t1 add age int not null default -1;
Query OK, 1 row affected (Elapsed: 00:00:00.67)
Records: 1  Duplicates: 1  Warnings: 0

gbase> alter table t1 add address varchar(100) not null default '未知地址';
Query OK, 1 row affected (Elapsed: 00:00:00.17)
Records: 1  Duplicates: 1  Warnings: 0

gbase> select * from t1 limit 10;
+------+--------+-----+--------------+
| id   | num    | age | address      |
+------+--------+-----+--------------+
|    1 | 121212 |  -1 | 未知地址     |
+------+--------+-----+--------------+
1 row in set (Elapsed: 00:00:00.20)