Serial is an object that creates a unique sequence number, and has the following characteristics.
- The serial is useful in creating a unique sequence number in a multi-user environment.
- Generated serial numbers are not related with table so, you can use the same serial in multiple tables.
You can create a serial object in the database by using the CREATE SERIAL statement.
CREATE SERIAL serial_identifier
[ START WITH initial ]
[ INCREMENT BY interval]
[ MINVALUE min | NOMINVALUE ]
[ MAXVALUE max | NOMAXVALUE ]
[ CYCLE | NOCYCLE ]
- serial_identifier : Specifies the name of the serial to be generated.
- START WITH initial : Specifies the initial value of serial with 38 digits or less. In the the ascending serial, that is its minimum value. In the descending serial, this is its maximum value.
- INCREMENT BY interval : Specifies the increment of the serial. You can specify any integer with 38 digits or less except for zero at interval. 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.
- 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 smaller 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. When a serial in ascending order reaches the maximum value, the minimum value is created as the next value; When a serial in descending order reaches the minimum value, the maximum value is created as the next value.
- NOCYCLE : Specifies that the serial will not be generated any more after reaching the maximum or minimum value. The default value is NOCYCLE.
- Example 1
- The following is an example of creating the serial object order_no whose initial, increment and maximum values are specified as 1, 1, and 1037 respectively.
CREATE SERIAL order_no;
- Example 2
- The following is an example of creating the serial object idx_no whose initial, increment and maximum values are specified as 10,000, 2, and 20,000 respectively.
CREATE SERIAL idx_no START WITH 10000 INCREMENT BY 2 MAXVALUE 20000;