DROP SEQUENCE

Synopsis

Use the DROP SEQUENCE statement to delete a sequence in the current schema.

Syntax

Grammar
Diagram

drop_sequence ::= DROP SEQUENCE [ IF EXISTS ] sequence_name 
                  [ CASCADE | RESTRICT ]

drop_sequence

Semantics

sequence_name

Specify the name of the sequence.

An error is raised if a sequence with that name does not exist in the current schema unless IF EXISTS is specified.
An error is raised if any object depends on this sequence unless the CASCADE option is specified.

CASCADE

Remove also all objects that depend on this sequence (for example a DEFAULT value in a table's column).

RESTRICT

Do not remove this sequence if any object depends on it. This is the default behavior even if it's not specified.

Examples

Dropping a sequence that has an object depending on it, fails.

yugabyte=# CREATE TABLE t(k SERIAL, v INT);

CREATE TABLE

\d t

                           Table "public.t"
 Column |  Type   | Collation | Nullable |           Default
--------+---------+-----------+----------+------------------------------
 k      | integer |           | not null | nextval('t_k_seq'::regclass)
 v      | integer |           |          |

yugabyte=#  DROP SEQUENCE t_k_seq;

ERROR:  cannot drop sequence t_k_seq because other objects depend on it
DETAIL:  default for table t column k depends on sequence t_k_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Dropping the sequence with the CASCADE option solves the problem and also deletes the default value in table t.

yugabyte=# DROP SEQUENCE t_k_seq CASCADE;

NOTICE:  drop cascades to default for table t column k
DROP SEQUENCE

\d t

                 Table "public.t"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 k      | integer |           | not null |
 v      | integer |           |          |