How to automatically detect a column with an auto-incrementing data type in Firebird?-CodePudding

Is it possible to find out if a Firebird table has an auto-increment column?

CodePudding user response：

Firebird 2.5 and earlier do not have auto-increment columns, and this is usually worked around by using triggers and sequences (a.k.a. generators), as shown on this page. There is no general way to detect this style of auto-increment column (though in specific cases, you may be able to infer things from naming conventions of triggers and/or sequences).

Firebird 3.0 introduced identity columns (GENERATED BY DEFAULT AS IDENTITY), and Firebird 4.0 extended this by also adding GENERATED ALWAYS AS IDENTITY).

It is possible to detect this type of auto-increment column by way of the RDB$IDENTITY_TYPE column of the system table RDB$RELATION_FIELDS, with values:

NULL - not an identity column
0 - identity column, GENERATED ALWAYS
1 - identity column, GENERATED BY DEFAULT

For example, to list all columns that are identity columns:

select 
  RDB$RELATION_NAME, 
  RDB$FIELD_NAME, 
  decode(RDB$IDENTITY_TYPE, 0, 'ALWAYS', 1, 'DEFAULT', 'unknown type') as IDENTITY_TYPE
from RDB$RELATION_FIELDS
where RDB$IDENTITY_TYPE is not null

However, keep in mind that even with Firebird 3.0 and higher, it is still possible tables use the legacy way of triggers sequences.