Adding auto increment identity to existing table in oracle which is not empty

Question

I was wondering how can I add an identity column to existing oracle table? I am using oracle 11g. Suppose I have a table named DEGREE and I am going to add an identity column to that.

FYI table is not empty.

Answer 1

From Oracle 12c you would use an identity column.

For example, say your table is called demo and has 3 columns and 100 rows:

create table demo (col1, col2, col3)
as
select dbms_random.value(1,10), dbms_random.value(1,10), dbms_random.value(1,10)
from   dual connect by rownum <= 100;

You could add an identity column using:

alter table demo add demo_id integer generated by default on null as identity;

update demo set demo_id = rownum;

Then reset the internal sequence to match the data and prevent manual inserts:

alter table demo modify demo_id generated always as identity start with limit value;

and define it as the primary key:

alter table demo add constraint demo_pk primary key (demo_id);

This leaves the new column at the end of the column list, which shouldn’t normally matter (except for tables with a large number of columns and row chaining issues), but it looks odd when you describe the table. However, we can at least tidy up the dictionary order using the invisible/visible hack:

SQL> desc demo
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 COL1                                      NUMBER
 COL2                                      NUMBER
 COL3                                      NUMBER
 DEMO_ID                          NOT NULL NUMBER(38)

begin
    for r in (
        select column_name from user_tab_columns c
        where  c.table_name = 'DEMO'
        and    c.column_name <> 'DEMO_ID'
        order by c.column_id
    )
    loop
        execute immediate 'alter table demo modify '||r.column_name||' invisible';
        execute immediate 'alter table demo modify '||r.column_name||' visible';
    end loop;
end;
/

SQL> desc demo
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 DEMO_ID                          NOT NULL NUMBER(38)
 COL1                                      NUMBER
 COL2                                      NUMBER
 COL3                                      NUMBER

One thing you can't do (as of Oracle 18.0) is alter an existing column to make it into an identity column, so you have to either go through a process like the one above but copying the existing values and finally dropping the old column, or else define a new table explicitly with the identity column in place and copy the data across in a separate step. Otherwise you'll get:

-- DEMO_ID column exists but is currently not an identity column:
alter table demo modify demo_id generated by default on null as identity start with limit value;

-- Fails with:
ORA-30673: column to be modified is not an identity column 

Answer 2

You can not do it in one step. Instead,

• Alter the table and add the column (without primary key constraint)

  ALTER TABLE DEGREE ADD (Ident NUMBER(10));
  

• Fill the new column with data which will fulfill the primary key constraint (unique/not null), e.g. like

  UPDATE DEGREE SET Ident=ROWNUM;
  

• Alter the table and add the constraint to the column

  ALTER TABLE DEGREE MODIFY (Ident PRIMARY KEY);
  

After that is done, you can set up a SEQUENCE and a BEFORE INSERT trigger to automatically set the id value for new records.