0

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

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
sid_com
  • 24,137
  • 26
  • 96
  • 187
  • What do you mean by "auto-increment column"? Firebird implements that kind of thing with [triggers and SEQUENCES](http://www.firebirdfaq.org/faq29/), rather than a magic attribute or data type. – pilcrow Jun 17 '22 at 15:15
  • 1
    @pilcrow Since Firebird 3.0, Firebird has identity columns (which are basically implemented with triggers and sequences, the column is identifiable has being an identity column). – Mark Rotteveel Jun 17 '22 at 15:53
  • Ah, looks like the FAQ (and I) needed an update, thanks. – pilcrow Jun 17 '22 at 15:57
  • @pilcrow I'm heavily involved in the Firebird project, but I have absolutely no idea who maintains that firebirdfaq.org site (assuming it is even still maintained); it is/was some community effort outside of the project. – Mark Rotteveel Jun 17 '22 at 16:09

2 Answers2

2

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
0

Here's a PHP function that retrieves the RDB$TRIGGER_BLR (BLOB) column as text, then parses it to get the column name

function getAutoIncrementColumns($table)
{
    $auto_increment_columns = array();
    // add your pdo connection here
    $stmt = $pdo->query('SELECT cast(RDB$TRIGGER_BLR as blob character set utf8) AS STRVAL FROM RDB$TRIGGERS WHERE RDB$SYSTEM_FLAG = 0 AND RDB$TRIGGER_TYPE=1 AND RDB$RELATION_NAME=\'YOUR_TABLE_HERE\'');

    $rows = $stmt->fetchAll(\PDO::FETCH_COLUMN);
    if ($rows) {
        foreach ($rows as $str) {
            if (preg_match('`blr_field,(?:\s?[0-9]+,\s?[0-9]+),\s?([^\s]+),`', $str, $out)) {
                $auto_increment_columns[] = str_replace(array('\'', ','), '', $out[1]);
            }
        }
    }

    return $auto_increment_columns;
}

Hopefully it may help someone someday

migli
  • 2,692
  • 27
  • 32