Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

CREATE SERIAL

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 multi-user environment.
  • Generated serial numbers are not related with table so, you can use the same serial in multiple tables.
  • All users including public can create a serial object. Once it is created, all users can get the number by using CURRENT_VALUE and NEXT_VALUE.
  • Only owner of a created serial object and dba can update or delete a serial object. If an owner is public, all users can update or delete it.
Description

You can create a serial object in the database by using the CREATE SERIAL statement.

Syntax

CREATE SERIAL serial_name

[ START WITH initial ]

[ INCREMENT BY interval]

[ MINVALUE min | NOMINVALUE ]

[ MAXVALUE max | NOMAXVALUE ]

[ CACHE integer | NOCACHE ]

  • 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 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)38 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.
  • CACHE : Saves 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. If the serials cached in the memory are used up, as many serials as " integer" are fetched again from the disk to the memory. If the database server stops midway through the process, all cached serial values are deleted. For this reason, the serial values before and after the restart of the database server may be discontinuous. Transaction rollback is not performed for cached serials, so the next serial requested becomes the next value of the last serial value previously requested even if rollback has been performed. 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 : 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.
Example 1

--creating serial with default values

CREATE SERIAL order_no

 

--creating serial within a specific range

CREATE SERIAL order_no START WITH 10000 INCREMENT BY 2 MAXVALUE 20000

--creating serial with specifying the number of cached serial values

CREATE SERIAL order_no START WITH 10000 INCREMENT BY 2 MAXVALUE 20000 CACHE 3

 

--selecting serial information from the db_serial class

SELECT * FROM db_serial

xr

 

=== < Result of SELECT Command in Line 1> ===

 

    name                       current_val           increment_val                 max_val                 min_val                 cyclic           started             cached_num               att_name

====================================================================================================================================================

'order_no'           10006                       2                                         20000                     10000                               0                       1                               3                       NULL

Example 2

The following is an example of creating the athlete_idx table to save athlete codes and names and then creating an instance by using the order_no. NEXT_VALUE increases the serial number and returns its value.

CREATE TABLE athlete_idx( code INT, name VARCHAR(40) )

CREATE SERIAL order_no START WITH 10000 INCREMENT BY 2 MAXVALUE 20000

INSERT INTO athlete_idx VALUES (order_no.NEXT_VALUE, 'Park')

INSERT INTO athlete_idx VALUES (order_no.NEXT_VALUE, 'Kim')

INSERT INTO athlete_idx VALUES (order_no.NEXT_VALUE, 'Choo')

INSERT INTO athlete_idx VALUES (order_no.CURRENT_VALUE, 'Lee')

SELECT * FROM athlete_idx

 

=== < Result of SELECT Command in Line 1> ===

 

                  code   name

===================================

                10000   'Park'

                10002   'Kim'

                10004   'Choo'

                10004   'Lee'