Reset sequence when all rows are deleted from a table

[Suresh Subbiah]

Original question from customer
How to reset identity value to 1? MySql example: ALTER TABLE tablename AUTO_INCREMENT = 1;

This DDL can be used.

>>showddl t ;

CREATE TABLE TRAFODION.SEABASE.T
  (
    ID LARGEINT GENERATED BY DEFAULT AS IDENTITY
      ( START WITH 1 INCREMENT BY 1 MAXVALUE 9223372036854775806 MINVALUE 1
       CACHE 25 NO CYCLE LARGEINT ) NOT NULL NOT DROPPABLE
  , B INT DEFAULT NULL
  )
;

-- The following sequence is a system created sequence --

CREATE SEQUENCE TRAFODION.SEABASE."_TRAFODION_SEABASE_T_ID_" /* INTERNAL */
  START WITH 1 /* NEXT AVAILABLE VALUE 26 */
  INCREMENT BY 1
  MAXVALUE 9223372036854775806
  MINVALUE 1
  CACHE 25
  NO CYCLE
  LARGEINT
;

After inserting some rows into the table, we do an unconditional delete. After the delete we would like the sequence to start again with with its min value.

snippet from original email conversation

  we currently don’t allow START WITH option to be altered as it might potentially lead to inconsistent behavior
of returning values that were already returned.
For ex, if original start was at 5, few values were generated, and then column was altered to
start at 4, it will then start to return 4,5, 6…
If that column was a primary key column, it will return unique key violation.
In your example, is the reset of identity col value done after some values have already been
returned? And duplicate values are ok if returned?
Or are you looking for more of the CYCLE option where values will start from beginning
once they reach the max?

We can allow ‘start with’ option to be altered as long as users are aware of
what the behavior will be (dup values being returned).
------------------------------
I want to reset the identity counter to 1 when I delete all the records in a table, now I am dropping the table and creating it just to reset the identity counter to 1.
------------------------------
I think that this happens only during development. In production, it is rare to reset the identity. If so, I think that this is of very low priority. Thanks!
---------------------------