Is it possible to find out if a Firebird table has an auto-increment column?
Advertisement
Answer
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.