# **CREATE SEQUENCE**

## **Grammar Description**

`CREATE SEQUENCE` The user creates a sequence object. A sequence is a special database object that can be used to automatically generate a unique sequence of numbers. Typically, a sequence is used to automatically generate unique values for the primary key field of a table.

`CREATE SEQUENCE` is a command used to create a sequence of self-increment numbers. It is used to generate unique, continuous numeric values, usually for generating values for primary key columns or other columns that require self-increment numbers.

## **Grammar Structure**

```sql
CREATE SEQUENCE [ IF NOT EXISTS ] SEQUENCE_NAME
    [ AS data_type ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue] [ MAXVALUE maxvalue]
    [ START [ WITH ] start ] [ [ NO ] CYCLE ]
```

### Syntax interpretation

#### data_type

Optional clause `AS data_type` specifies the data type of the sequence. Supported data types are `smalllint [unsigned]`, `integer [unsigned]` and `bigint [unsigned]`, and the default `bigint` is the default value. The data type determines the default minimum and maximum values of the sequence.

#### INCREMENT

Optional clause `INCREMENT [ BY ] increment` Specifies which value to add to the current sequence value to create a new value. Positive values will generate ascending order, negative values will generate descending order; the default value is 1.

#### MINVALUE

Optional clause `MINVALUE minvalue` determines the minimum value a sequence can generate. If this clause is not provided or `MINVALUE` is not specified, the default value is used. The default value for ascending sequences is 1. The default value of a descending sequence is the minimum value of the data type.

#### MAXVALUE

Optional clause `MAXVALUE maxvalue` determines the maximum value of the sequence. If this clause is not provided or `MAXVALUE` is not specified, the default value is used. The default value of an ascending sequence is the maximum value of the data type. The default value for descending sequences is -1.

#### START

Optional clause `START [ WITH ] start` Allows the sequence to start anywhere. The default start value is the minimum value of the ascending sequence and the maximum value of the descending sequence.

#### CYCLE

The `CYCLE` option allows the sequence to be wound back when ascending or descending sequences reach the maximum or minimum value, respectively. If the limit is reached, the next number generated will be the minimum or maximum value, respectively.

### Syntax Operation

Operate the value of the sequence, using the function:

#### `NEXTVAL(sequence_name)`

Set the current value to the incremented value and return.

#### `CURRVAL(sequence_name)`

Returns the current value.

#### `SETVAL(sequence_name, n [,b])`

Set the current value;

- b Default setting is true, the next time nextval() is called, it will directly return n
- b If false is set, return n+increment

#### `LASTVAL()`

Returns the value of any sequence that was most recently obtained with `NEXTVAL` in the current session, and `LASTVAL()()` can only be initialized by `NEXTVAL`.

`LASTVAL()` is affected by `SETVAL(sequence_name, n [,true])` changing the current value. The example is as follows:

Suppose a Sequence named `seq_id` is created, with a starting value of 1, each time incremented by 1, and a maximum value of 1000:

```sql
CREATE SEQUENCE seq_id INCREMENT BY 1 MAXVALUE 1000 START with 1;
```

You can then use the `NEXTVAL()` function to get the next sequence value and automatically increase the counter of the sequence by 1:

```sql
SELECT NEXTVAL('seq_id');
```

Next, you can use the `LASTVAL()` function to return the current value of the current Sequence:

```sql
SELECT LASTVAL();
```

You can also use the `SETVAL()` function to set the current value to 30, and the parameter `[,b]` is true:

```sql
SELECT SETVAL('seq_id', 30);
```

Then, you can use the `NEXTVAL()` function again to get the next sequence value:

```sql
SELECT NEXTVAL('seq_id');
```

31 will be returned because the current value has been set to 30, and the `NEXTVAL()` function will return the next sequence value 31.

```sql
SELECT LASTVAL();
```

Use `LASTVAL()` to return the current value of the current Sequence, which will return 31.

The above example shows that if the current value is set first through `SETVAL(sequence_name, n [,true])` and then use `NEXTVAL` to obtain the next sequence value, then use `LASTVAL()` again, and return the value of the sequence obtained by `NEXTVAL`.

### Use `SEQUENCE` in the table

To use a sequence in a table, you need to complete the following steps:

1. Create a sequence object: You can use the following SQL command to create a sequence named "my_sequence":

    ```sql
    CREATE SEQUENCE my_sequence;
    ```

    This will create a simple sequence object that will start at 1 and increment in 1.

2. Apply a sequence to a field in a table: In order to apply a sequence to a field in a table, you need to specify a default value in the table definition as the next value of the sequence, as shown below:

    ```sql
    CREATE TABLE my_table (
      id INTEGER DEFAULT nextval('my_sequence'),
      name VARCHAR(50));
    ```

    In the example above, the "id" field will automatically get the next unique value from the sequence as its default value.

3. Insert data: After the table and sequence have been defined, you can use the `INSERT` statement to insert data into the table. When inserting a row of data, if no value of the "id" field is specified, MatrixOne will automatically obtain the next unique value from the sequence as its default value.

    For example, the following statement will insert a row of data into the "my_table" table and automatically assign a unique value to the "id" field:

    ```sql
    INSERT INTO my_table (name) VALUES ('John');
    INSERT INTO my_table (name) VALUES ('Tom');
    ```

4. By using sequences, unique identifiers can be easily assigned automatically in the table, avoiding the errors that can result from manual identifier allocation. Use the following statement for query verification:

    ```text
    mysql> select * from my_table;
    +------+------+
    | id | name |
    +------+------+
    | 1 | John |
    | 2 | Tom |
    +------+------+
    2 rows in set (0.01 sec)
    ```

:::{note}
When using SEQUENCE in a table, you need to note that `auto_increment` and `sequence` cannot be used together, otherwise an error will be reported.
:::
## **Example**

```text
-- Created a sequence called "seq_id" which starts with 1 and increases by 1 each time, with a maximum value of 1000:
CREATE SEQUENCE seq_id INCREMENT BY 1 MAXVALUE 1000 START with 1;
-- After creating the sequence, you can use the NEXTVAL function to get the next sequence value as follows:
mysql> SELECT NEXTVAL('seq_id');
+-----------------------+
| nextval(seq_id) |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.02 sec)
-- This command returns the next value in the sequence (for example, 1) and automatically increments the counter of the sequence by 1.

-- The CURRVAL function returns the current value.
mysql> SELECT CURRVAL('seq_id');
+-----------------------+
| currval(seq_id) |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.01 sec)

-- Returns the value of any sequence that was most recently obtained with NEXTVAL in the current session.
mysql> SELECT LASTVAL();
+---------------+
| lastval() |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)

-- Set the current value to 30.
mysql> SELECT SETVAL('seq_id', 30);
+-------------------------+
| setval(seq_id, 30) |
+-------------------------+
| 30 |
+-------------------------+
1 row in set (0.02 sec)

-- NEXTVAL function gets the next sequence value.
mysql> SELECT NEXTVAL('seq_id');
+-----------------------+
| nextval(seq_id) |
+-----------------------+
| 31 |
+-----------------------+
1 row in set (0.00 sec)
```