Search code examples
firebirdidentity-columnfirebird-3.0

How to reset Firebird 3.0 identity column

I am using Firebird 3 for my project and I added identity columns for auto-increment columns.

Before publishing it, I tested the database with thousands of records now I want to reset the column back to zero but I couldn't find any solution.

Here is the image enter image description here

Solution

  • From the Firebird 3 release notes on identity columns:

    <alter column definition> ::=
    <name> RESTART [ WITH <value> ]

    A column definition can be altered to modify the starting value of the generator. RESTART alone resets the generator to zero; the optional WITH <value> clause allows the restarted generator to start at a value other than zero.

    For more information, see also ticket CORE-4206.

    In other words, to restart the identity sequence, you can use

    alter table yourtable alter column idcolumn restart

    Where yourtable should be the name of your table, and idcolumn the name of the identity column that needs to be reset.

    I don't know which tool you show in the screenshot, but apparently it doesn't support this option in its user interface. That means you'll need to execute the DDL statement shown above directly. Consider filing an improvement request with the maintainer of that tool if you think it is important.