The DROP SEQUENCE statement removes a sequence from a database.
Required privileges
The user must have the DROP privilege on the specified sequence(s).
Synopsis
Parameters
| Parameter | Description |
|---|---|
IF EXISTS |
Drop the sequence only if it exists; if it does not exist, do not return an error. |
sequence_name |
The name of the sequence you want to drop. Find the sequence name with SHOW CREATE on the table that uses the sequence. |
RESTRICT |
(Default) Do not drop the sequence if any objects (such as constraints and tables) use it. |
CASCADE |
Not yet implemented. Currently, you can only drop a sequence if nothing depends on it. |
Examples
Remove a sequence (no dependencies)
In this example, other objects do not depend on the sequence being dropped.
> SELECT * FROM information_schema.sequences;
+------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
| sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option |
+------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
| def | db_2 | test_4 | INT | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO |
| def | test_db | customer_seq | INT | 64 | 2 | 0 | 101 | 1 | 9223372036854775807 | 2 | NO |
| def | test_db | desc_customer_list | INT | 64 | 2 | 0 | 1000 | -9223372036854775808 | -1 | -2 | NO |
| def | test_db | test_sequence3 | INT | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO |
+------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
(4 rows)
> DROP SEQUENCE customer_seq;
DROP SEQUENCE
> SELECT * FROM information_schema.sequences
+------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
| sequence_catalog | sequence_schema | sequence_name | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option |
+------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
| def | db_2 | test_4 | INT | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO |
| def | test_db | desc_customer_list | INT | 64 | 2 | 0 | 1000 | -9223372036854775808 | -1 | -2 | NO |
| def | test_db | test_sequence3 | INT | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO |
+------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+----------------------+---------------------+-----------+--------------+
(4 rows)