Help Center/ GaussDB/ Developer Guide(Centralized_3.x)/ Database Quick Start/ Operating a Database/ Other Operations/ Creating and Managing Sequences
Updated on 2024-05-07 GMT+08:00
View PDF
Share

Creating and Managing Sequences

Context

A sequence is a database object that generates unique integers. Sequence numbers are generated according to a certain rule. Sequences are unique because they increase automatically. This is why they are often used as primary keys.

You can create a sequence for a column in either of the following methods:
  • Set the data type of a column to sequence integer. A sequence will be automatically created by the database for this column.
  • Use the CREATE SEQUENCE statement to create a sequence. Set the initial value of the nextval('sequence_name') function to the default value of a column.

Procedure

Method 1: Set the data type of a column to a sequence integer. Example: 
1
2
3
4
5
 
gaussdb=# CREATE TABLE T1
(
    id    serial,
    name  text
);

If the following information is displayed, the creation is successful:

1
 
CREATE TABLE

Method 2: Create a sequence and set the initial value of the nextval('sequence_name') function to the default value of a column.

  1. Create a sequence. 
    1
    		 
    gaussdb=# CREATE SEQUENCE seq1 cache 100;

    If the following information is displayed, the creation is successful:

    1
    		 
    CREATE SEQUENCE
  2. Set the default value of a column so that the column has a unique identification attribute. 
    1
2
3
4
5
    		 
    gaussdb=# CREATE TABLE T2 
( 
    id   int not null default nextval('seq1'),
    name text
);

    If the following information is displayed, the default value has been specified:

    1
    		 
    CREATE TABLE
  3. Associate a sequence with a column.

    Associate a sequence with a specified column included in a table. In this way, the sequence will be deleted when you delete its associated column or the table where the column belongs to.

    1
    		 
    gaussdb=# ALTER SEQUENCE seq1 OWNED BY T2.id;

    If the following information is displayed, the operation is successful:

    1
    		 
    ALTER SEQUENCE

The preceding methods are similar, except that the second method specifies cache for the sequence. A sequence having cache defined has inconsecutive values (such as 1, 4, and 5) and cannot maintain the order of its values. After the dependent column of a sequence has been specified, once the sequence is deleted, the sequence of the dependent will be deleted. A sequence shared by multiple columns is not forbidden in a database, but you are advised not to do that.

In the current version, you can specify the auto-increment column or set the default value of a column to nextval('seqname') when defining a table. You cannot add an auto-increment column or a column whose default value is nextval('seqname') to an existing table.

Parent topic: Other Operations