自增列

在数据库中，自增列用于自动生成唯一的数值，通常作为表的主键。

虚谷数据库通过INDENTITY(B,S)语法和AUTO_INCREMENT语法实现自增列。控制自增列的插入值填充模式，可通过系统参数def_identity_mode或会话级参数IDENTITY_MODE进行设置。

自增列是一组序列值，可以通过系统表sys_columns获取对应自增列的序列值发生器的ID号，即SERIAL_ID，再通过SERIAL_ID在系统表sys_sequences中查看对应自增列的详细信息。

定义自增列

语法格式

CREATE TABLE table_name (col_name INT {IDENTITY IDENTITY[(B,S)]|AUTO_INCREMENT} [col_elements]);

参数说明

• table_name：表名。
• col_name：列名。
• IDENTITY[(B,S)]：设置自增列，B为初始值，S为步长，均为INTEGER类型，(B,S)省略时默认为(1,1)。
• AUTO_INCREMENT：设置自增列，效果同IDENTITY(1,1)。
• [col_elements]：可选其他列定义操作，如设置主键约束、值检查约束、外键约束，添加列注释等，详细信息请参见创建表章节。

修改自增列

语法格式

ALTER TABLE table_name {ALTER|MODIFY} [COLUMN] col_name INT IDENTITY[(B,S)] [alter_specification];

参数说明

• table_name：表名。
• {ALTER|MODIFY}：修改列关键字，两者效果相同。
• [COLUMN]：列关键字，可省略。
• col_name：列名。
• IDENTITY[(B,S)]：设置自增列，B为初始值，S为步长，均为INTEGER类型，(B,S)省略时默认为(1,1)。
• [alter_specification]：可选其他列修改操作，修改约束、修改注释等，详细信息请参见修改表章节。

示例

创建一个自增列初始值为0，步长为-1的表，并插入值。

SQL> CREATE TABLE t1 (id INT IDENTITY(0,-1) PRIMARY KEY, name VARCHAR(50)); 

SQL> INSERT INTO t1 (name) VALUES ('小白'),('小何');   

SQL> SELECT * FROM t1;

ID | NAME | 
------------------------------------------------------------------------------
0 | 小白|
-1 | 小何|

修改自增列的初始值和步长，再次插入值。

SQL> ALTER TABLE t1 ALTER id INT IDENTITY(1,5); 

SQL> INSERT INTO t1 (name) VALUES ('小张'),('小王');  

SQL> SELECT * FROM t1;

ID | NAME | 
------------------------------------------------------------------------------
0 | 小白|
-1 | 小何|
1 | 小张|
6 | 小王|

去掉自增列属性，再次插入值。

SQL> ALTER TABLE t1 ALTER id INT; 

SQL> INSERT INTO t1 (name) VALUES ('小李'),('小赵');   

SQL> SELECT * FROM t1;

ID | NAME | 
------------------------------------------------------------------------------
0 | 小白|
-1 | 小何|
1 | 小张|
6 | 小王|
<NULL>| 小李|
<NULL>| 小赵|

创建一个具有自增列的表，通过连接查询查看自增列的CURR_VAL当前值和STEP_VAL增长步长。

SQL> CREATE TABLE t2 (id INT IDENTITY(5,2)); 

SQL> SELECT 
        CURR_VAL, STEP_VAL
    FROM 
        sys_sequences seq
    INNER JOIN 
        sys_columns col 
    ON 
        seq.SEQ_ID = col.SERIAL_ID
    INNER JOIN 
        sys_tables tab 
    ON 
        col.TABLE_ID = tab.TABLE_ID
    WHERE 
        tab.TABLE_NAME = 't2';

CURR_VAL | STEP_VAL | 
------------------------------------------------------------------------------
5 | 2 |