Add auto generated key to existing PK in Oracle

Question

I'm porting a SQL Server based app to Oracle. Our Oracle DBA has given me a schema that was supposed to be identical to the original SQL Server schema (and generated from it), but the auto generated keys are missing. I am trying to alter these table PK's from a normal INT to incrementing. I am doing so with Oracle SQL Developer 4.0.3 and Oracle 12c.

The error I receive is ORA-01442: column to be modified to NOT NULL is already NOT NULL I get this after editing the table, selecting the column and setting it's Identity dropdown to 'Generated as Identity'. I am not sure why SQl Developer is attempting to make it not null when it's already a PK.

My questions are: Is this the proper way to setup a generated key? How can I get around this? If I go alter all the required columns, can the DBA use the schema to regenerate whatever procedure he used to create it in the first place to allow proper generated keys and is there a better solution for creating a good schema to go forward with?

Thanks.

Luis C Luis C · Accepted Answer · 2016-04-13T17:13:45

If the column is already definied as NOT NULL there is no need to re-defined it as NOT NULL. Therefore you get the error ora-01442.

The best way to obtain sequence values, such as identity in SQL Server, is define the column with default sequence, before inserting row:

CREATE SEQUENCE SEQ_NAME
START WITH     1
INCREMENT BY   1
NOCACHE
NOCYCLE;

ALTER TABLE table_name MODIFY column_name INT DEFAULT SEQ_NAME.NEXTVAL;

PD: This DEFAULT works with 12 c. To 11g or less, you must create a trigger

Add auto generated key to existing PK in Oracle

1 Answers