DROP SEQUENCE

DROP SEQUENCE — remove a sequence

Syntax

DROP SEQUENCE [ IF EXISTS ] [schema.]sequence_name
DROP SEQUENCE  [schema.]sequence_name [ IF EXISTS ]

Description

Use the DROP SEQUENCE statement to remove a sequence from the database. You cannot drop a sequence that is defined as the sequence of a generated by identity table column (see Example 1). You can also use this statement to restart a sequence by dropping and then re-creating it.

Sequences created for autogenerated columns are automatically dropped when the column or table are dropped.

Parameters

IF EXISTS: If the sequence does not exist and you specify I F EXISTS, NuoDB does not generate an error. Otherwise, if the sequence does not exist, an error is generated. IF EXISTS may be specified either before or after the sequence name.
schema: Optional. The name of the schema that owns the sequence to be dropped. If schema is not provided, the sequence must be owned by the current schema.
sequence_name: Optional. The name of the schema that owns the sequence to be dropped. If schema is not provided, the sequence must be owned by the current schema.

Examples

Example 1: Attempt to drop a sequence that is defined as a sequence for a table column.

USE TEST;
CREATE SEQUENCE tst_seq START WITH 10;
CREATE TABLE tst_table
       (id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY (tst_seq)
       ,column1 string);
INSERT INTO tst_table (column1) VALUES ('column1 string value');
DROP SEQUENCE tst_seq;
sequence "TEST.TST_SEQ" is referenced by table TEST.TST_TABLE and cannot be dropped

DROP TABLE tst_table;
DROP SEQUENCE tst_seq;

Example 2: Drop a sequence that does not exist

DROP SEQUENCE IF EXISTS nosuchsequence;
DROP SEQUENCE nosuchsequence IF EXISTS;

Example 3: Sequence created as Identity is automatically dropped when table is dropped

USE TEST;
CREATE TABLE tst_table
       (id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY
       ,column1 string);
INSERT INTO tst_table (column1) VALUES ('column1 string value');
SHOW TABLE tst_table;
    Found table TST_TABLE in schema TEST

        Fields:
            ID integer
                Nullable: No
                Generator: TST_TABLE$IDENTITY_SEQUENCE    Generated Always
            COLUMN1 string
        Primary Index: TST_TABLE..PRIMARY_KEY on field: ID

DROP SEQUENCE TST_TABLE$IDENTITY_SEQUENCE;
sequence "TEST.TST_TABLE$IDENTITY_SEQUENCE" is referenced by table TEST.TST_TABLE and cannot be dropped

DROP TABLE tst_table;
DROP SEQUENCE TST_TABLE$IDENTITY_SEQUENCE;
sequence TEST.TST_TABLE$IDENTITY_SEQUENCE doesn't exist