With the ALTER SERIAL statement, you can update the increment of the serial value, set or delete its initial or minimum/maximum values, and set its cycle attribute.
ALTER SERIAL serial_identifier
[ INCREMENT BY interval ]
[ START WITH initial_value ]
[ MINVALUE min | NOMINVALUE ]
[ MAXVALUE max | NOMAXVALUE ]
[ CACHE integer | NOCACHE ]
- serial_identifier: Specifies the name of the serial to be created.
- INCREMENT BY interval: Specifies the increment of the serial. For the interval, you can specify any integer with 38 digits or less except zero. The absolute value of the interval must be smaller than the difference between MAXVALUE and MINVALUE. If a negative number is specified, the serial is in descending order; otherwise, it is in ascending order. The default value is 1.
- START WITH initial_value: Changes the initial value of Serial.
- MINVALUE: Specifies the minimum value of the serial with 38 digits or less. MINVALUE must be smaller than or equal to the initial value and smaller than the maximum value.
- NOMINVALUE: 1 is set automatically as a minimum value for the ascending serial; -(10)36 for the descending serial.
- MAXVALUE: Specifies the maximum number of the serial with 38 digits or less. MAXVALUE must be larger than or equal to the initial value and greater than the minimum value.
- NOMAXVALUE: (10)37 is set automatically as a maximum value for the ascending serial; -1 for the descending serial.
- CYCLE: Specifies that the serial will be generated continuously after reaching the maximum or minimum value. If the ascending serial reaches the maximum value, the minimum value is generated as the next value. If the descending serial reaches the minimum value, the maximum value is generated as the next value.
- NOCYCLE: Specifies that the serial will not be generated any more after reaching the maximum or minimum value. The default is NOCYCLE.
- CACHE: Stores as many serials as the number specified by integer in the cache to improve the performance of the serials and fetches a serial value when one is requested. The integer after the CACHE keyword cannot be omitted. If a number equal to or smaller than 1 is specified, the serial cache is not applied.
- NOCACHE: It does not use the serial cache feature. The serial value is updated every time and a new serial value is fetched from the disk upon each request.
Warning 1 In CUBRID 2008 R1.x version, the serial value can be modified by updating the db_serial table, a system catalog. However, in CUBRID 2008 R2.0 version or above, the modification of the db_serial table is not allowed but use of the ALTER SERIAL statement is allowed. Therefore, if an ALTER SERIAL statement is included in the data exported (unloaddb) from CUBRID 2008 R2.0 or above, it is not allowed to import (loaddb) the data in CUBRID 2008 R1.x or below.
Warning 2 In version lower than CUBRID 9.0, the next value of the initial value set as ALTER SERILAL is returned when the first NEXT_VALUE value is calculated after ALTER SERIAL. However, in version of CUBRID 9.0 or higher, the setting value of ALTER_SERILAL is returned.
--altering serial by changing start and incremental values
ALTER SERIAL order_no START WITH 100 INCREMENT BY 2;
--altering serial to operate in cache mode
ALTER SERIAL order_no CACHE 5;
--altering serial to operate in common mode
ALTER SERIAL order_no NOCACHE;