How to cycle an identity column's value back to 1 on reaching a limit?

Question

I am creating a table and I am setting an automatically generated value for it, starting from 1, but I would like this value to have a limit and then start again from 1.

In what way should I define the column for this to be like this?

CREATE TABLE MYTABLE
(
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
    NAME VARCHAR(100)
);

Answer 1

Here is one option:

CREATE TABLE MYTABLE (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY (
        START WITH 1
        INCREMENT BY 1 
        MAXVALUE 10
        CYCLE
        NOCACHE
    ),
    NAME VARCHAR(100)
);

Since the CYCLE option is enabled, the sequence resets when it reaches the value specified in MAXVALUE. I added the NOCACHE option although that's not strictly necessary here (the only requirement is that the CACHE value must be smaller than MAXVALUE).

Here is a DB Fiddle demo, which MAXVAL set to 2: you can see that the third inserted row gets id 1.