Description
This statement creates an auto-increment column.
Syntax
CREATE SEQUENCE sequence_name
[MINVALUE value | NOMINVALUE]
[MAXVALUE value | NOMAXVALUE]
[START WITH value]
[INCREMENT BY value]
[CACHE value | NOCACHE]
[ORDER | NOORDER]
[CYCLE | NOCYCLE];
Parameter description
| Parameter | Description |
|---|---|
| MINVALUE value | NOMINVALUE | Specifies the minimum value of the auto-increment column. Valid values: -10^27^ to (10^27^-1). If you set this parameter to NOMINVALUE, the minimum value is 1 for the ascending order and the minimum value is -(10^27^-1) for the descending order. If no parameter is specified, the default value is NOMINVALUE. |
| MAXVALUE value | NOMAXVALUE | Specifies the maximum value of the auto-increment column. Valid values: (-10^27^+1) to 10^27^. If you set this parameter to NOMAXVALUE, the maximum value is (10^28^-1) for the ascending order and the maximum value is -1 for the descending order. If no parameter is specified, the default value is NOMAXVALUE. |
| START WITH value | Specifies the start value of the auto-increment column. This value must be less than or equal to MAXVALUE and greater than or equal to MINVALUE. If no parameter is specified, the default value is the minimum value for the ascending order and the default value is the maximum value for the descending order. |
| INCREMENT BY value | Specifies the auto-increment step for the auto-increment column. The value cannot be 0. If you specify this parameter as a positive number, the auto-increment column is in ascending order. If you specify this parameter as a negative number, the auto-increment column is in descending order. If no parameter is specified, the default value is 1. |
| CACHE value | NOCACHE | Specifies the number of preassigned auto-increment values in the memory. Default value: 20. |
| ORDER | NOORDER | Specifies whether the values of the auto-increment column are generated in sequence. Default value: NOORDER. |
| CYCLE | NOCYCLE | Specifies whether the values of the auto-increment column are cyclically generated. Default value: NOCYCLE. |
Examples
- Create an auto-increment column s1.
OceanBase(ADMIN@TEST)>create sequence S1 minvalue 1 maxvalue 5 nocycle noorder cache 10240000;
Considerations
If you specify both MINVALUE and MAXVALUE, MINVALUE must be smaller than MAXVALUE.
The difference between MAXVALUE and MINVALUE must be greater than or equal to INCREMENT BY value.
The value of the CACHE value must be greater than 1. If the value of CACHE value is 1, the value is equivalent to NOCACHE.
If you specify CYCLE and INCREMENT BY value is smaller than 0, you must specify MINVALUE.
If you specify CYCLE, the number of caches cannot exceed one cycle.
