Adding an identity column to an existing table

[Ertan Küçükoglu]

Hello,

I am using FirebirdSQL 5.0 and I need to add an identity column to an existing table.

There is no primary key on that table. I just need to add this identity column and it should be filled with values.

I am executing below SQL

alter table groupaccountingplan add autoinc integer generated always as identity

I get error saying

Can't perform operation on inactive transaction

Is it possible to do that? I might be missing something but I don't know what.

Any help would be appreciated.

Thanks & Regards,

Ertan

[Dimitry Sibiryakov]

Ertan Küçükoglu wrote 26.07.2024 15:01:
> Is it possible to do that?

No, you cannot add identity column to table that already contain data.
Even if you start transaction, the error will be "Cannot make field AUTOINC
of table GROUPACCOUNTINGPLAN NOT NULL because there are NULLs present".

--
WBR, SD.

[Mark Rotteveel]

On 26/07/2024 15:01, Ertan Küçükoglu wrote:
> Hello,
>
> I am using FirebirdSQL 5.0 and I need to add an identity column to an
> existing table.
> There is no primary key on that table. I just need to add this identity
> column and it should be filled with values.
>
> I am executing below SQL
>
> alter table groupaccountingplan add autoinc integer generated always as
> identity
>
> I get error saying
>
> Can't perform operation on inactive transaction
>
> Is it possible to do that? I might be missing something but I don't know
> what.

The error you get is not a Firebird error.

As Dimitry already said, it is not possible to add an identity column to
a table that already has data. An identity column is implicitly NOT
NULL, but it will not generate something for existing rows.

See also the third point in the caution admonition at
https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref50/firebird-50-language-reference.html#fblangref50-ddl-tbl-altradd

Mark
--
Mark Rotteveel