Example: Updating IDENTITY defined as GENERATED ALWAYS

Create a table with primary key as an IDENTITY column defined as GENERATED ALWAYS AS IDENTITY and insert data to the table.

CREATE TABLE Test_sqlUpdateAlways (
    idValue INTEGER GENERATED ALWAYS AS IDENTITY,
    name STRING,
PRIMARY KEY(idValue))

INSERT INTO Test_sqlUpdateAlways VALUES (DEFAULT, 'joe')

INSERT INTO Test_sqlUpdateAlways VALUES (DEFAULT, 'jasmine')

The Test-sqlUpdateAlways table will have the following rows:

1, 'joe'
2, 'jasmine'

Example 7-36 Error scenario - Update IDENTITY column defined as GENERATED ALWAYS

You can update the idValue column as follows:

UPDATE Test_sqlUpdateAlways SET idValue = 10 WHERE name=joe

The UPDATE statement above will raise an exception saying that a user cannot set a value for an IDENTITY column that is defined as GENERATED ALWAYS. An IDENTITY column that is defined as GENERATED ALWAYS cannot be updated. Only the IDENTITY column that is defined as GENERATED BY DEFAULT can be updated.

To resolve this exception and be able to update the IDENTITY column value, you must alter the IDENTITY column and change the property of the IDENTITY column to GENERATED BY DEFAULT. But there can be implications to the existing data. For more information on how to alter an IDENTITY column see, Altering an IDENTITY Column.