During my presentation "Oracle 12c for Developers" at the Sloveninan and Croatian User Groups I got the same question twice about Identity Columns:
Is it possible to update an Identity Column?During the presentation I show how it is not possible to insert a value for a "Generated Always Identity" column.
Let's take a look at an example:
SQL> create table t
2 (id number generated as identity
3 ,name varchar2(35)
4 );
Table created.
SQL> insert into t(name) values ('hello')
2 /
1 row created.
In the first section the table is created with a "Generated Always Identity" column.The second part shows that you can insert into the table, as long as the identity column isn't used in the insert statement. Trying to do so will lead to an exception.
SQL> insert into t values (1, 'World') 2 / insert into t values (1, 'World') * ERROR at line 1: ORA-32795: cannot insert into a generated always identity columnThe same is true when you attempt to insert a record type:
SQL> declare 2 r t%rowtype; 3 begin 4 r.id := 42; 5 r.name := 'world'; 6 insert into t values r; 7 end; 8 / declare * ERROR at line 1: ORA-32795: cannot insert into a generated always identity column ORA-06512: at line 6Now back to the question, what about an update? Let's try some different methods:
SQL> update t
2 set id = id
3 /
set id = id
*
ERROR at line 2:
ORA-32796: cannot update a generated always identity column
SQL> update t
2 set id = id + 1
3 /
set id = id + 1
*
ERROR at line 2:
ORA-32796: cannot update a generated always identity column
SQL> update t
2 set id = id + 0
3 /
set id = id + 0
*
ERROR at line 2:
ORA-32796: cannot update a generated always identity column
In short: updating a "Generated Always Identity" column is not allowed either.One final remark: an identity column is not the same as a primary key. If you want to use a "Generated Always Identity" column as your primary key, then you will have to specify that explicitly.
SQL> create table t 2 (id number generated as identity primary key 3 ,name varchar2(35) 4 ); Table created.
