Primary Key as Generated Identity

Be careful with identity columns.

Primary Key as Generated Identity

From Oracle 12c you can have Oracle generate the primary key for you. As an example:

create table customer (
   id number generated always as identity,
   name varchar2(50),
   constraint customer_pk primary key (id));

Later you can remove this identity with an alter table command, but you can’t add it back!

SQL> alter table customer modify (id drop identity);

Table CUSTOMER altered.

SQL> alter table customer modify (id number generated always as identity);

Error starting at line : 1 in command -
alter table customer modify (id number generated always as identity)
Error report -
ORA-30673: column to be modified is not an identity column
30673.0000 -  "column to be modified is not an identity column"
*Cause:    An attempt was made to modify the identity properties of
           that is not an identity column.
*Action:   Modify the identity properties of an identity column.

You can check the documentation for 21c , there is no other syntax to solve this issue.

You can drop the column and add it back, but dropping the primary key may be a problem if you have foreign keys in other tables referring to it.

One option is to use online table redefinition .

By the way, unless you specify more options than given in the example above, the system-generated sequence will have the same defaults as a normal sequence. You can change this as shown in this example (provided that the column is already created as an identity column):

alter table customer modify (id number generated always as identity nocache order);

I hope your design does not require the attributes set as shown above, but I have recently seen two systems that depended on it. One API was using the ID-column like a virtual clock; the ID-values had to be created in strictly increasing order and no gaps. If you know something about clusters and hot blocks you’ll know that does not scale well.

There is one option here I don’t understand the value of. You can define the system-generated sequence to be used when noe value is given for the ID-column. But unless you make sure that supplied ID-values are in another range it will likely clash with sequence:

create table customer (
   id number generated by default on null as identity,
   name varchar2(50),
   constraint customer_pk primary key (id));

SQL> insert into customer(id,name) values (1,'Fulano');

1 row inserted.

SQL> insert into customer(name) values ('Somebody');

Error starting at line : 1 in command -
insert into customer(name) values ('Somebody')
Error report -
ORA-00001: unique constraint (OISENE.CUSTOMER_PK) violated

But here is the fix for that problem:

alter table customer modify (id generated always as identity (start with limit value));

Table CUSTOMER altered.

SQL> insert into customer(name) values ('Somebody');

1 row inserted.

SQL> select * from customer;

   ID        NAME
_____ ___________
    1 Fulano
    2 Somebody

This will change the sequence to start one above the maximum value for ID in the table, and any attempt to insert with a specified ID-value will fail:

insert into customer(id,name) values (42,'Someone else')
Error at Command Line : 1 Column : 22
Error report -
SQL Error: ORA-32795: cannot insert into a generated always identity column
32795.0000 -  "cannot insert into a generated always identity column"
*Cause:    An attempt was made to insert a value into an identity column
           created with GENERATED ALWAYS keywords.
*Action:   A generated always identity column cannot be directly inserted.
           Instead, the associated sequence generator must provide the value.

Obviously this is what we want, to fail immediately in case of a coding error, not sometime in the future when the sequence reaches an already inserted value.