How to use custom sequence name with GENERATED BY DEFAULT AS IDENTITY

[MarvinKlein1508]

Hi

when running this script in Firebird 4 it generated a new sequence for the table.

CREATE TABLE USERS (
  user_id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
  username VARCHAR(50)
);

The sequence is always named RDB$(number of tables created by GENERATED BY DEFAULT AS IDENTITY)

However I want to be able to see which sequence is for what in my database so I want to use a custom name for this for example GEN_USERS_USER_ID

Is there any way to achieve this or to rename the generated sequence?

[livius2]

Why do you need to know that, if you cannot manipulate it, or do anything with it? It is system object and IDENTITY implementation detail

[aafemt]

On the other hand index for constraint is also an implementation detail but Firebird allows to set its name explicitly.

[mrotteveel]

That sounds like entirely unnecessary goldplating in this case.

[mrotteveel]

This is not possible, as Karol says, the backing generator of an identity column and its name are an implementation detail. If you want to use a custom sequence name, you cannot use an identity column, and instead you need to use a custom trigger (the way of defining auto-increment columns before the introduction of identity columns).

Maybe you should file an improvement request with the maker of the tool (it looks like DBeaver) to hide these system sequences (just like it does for "normal" system sequences with RDB$SYSTEM_FLAG = 1). Sequences backing an identity column have RDB$SYSTEM_FLAG = 6.

It looks like this tool is currently excluding sequences with RDB$SYSTEM_FLAG = 1, while it should only include sequences with RDB$SYSTEM_FLAG = 0 (all non-zero system flag values indicate it is a system object), or possibly coalesce(RDB$SYSTEM_FLAG, 0) = 0 , because in older versions the system flag was sometimes set to NULL instead of 0.

[mrotteveel]

Note that I converted the issue to a discussion.

[MarvinKlein1508]

I want to use a custom name to reseed the sequence for the table at any given time.

Before identity I could alter the sequence itself and change the start value. I could change the RDB$6 sequence as well but now I need to figure out which sequence is being used by my table to achieve this.

[aafemt]

For this you can (and should) use ALTER TABLE ALTER COLUMN query.

[mrotteveel]

As Dimitry points out, you should use the DDL that is designed for managing the identity columns. The fact identity columns are backed by a sequence is an implementation detail, and as such you should not mess with those sequences like you would with normal sequences, even if you can (personally, I think we should not allow things like GEN_ID, SET GENERATOR or ALTER SEQUENCE on these system objects, but I guess that makes it also more complicated for the internals themselves).