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.

Solution 1

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.

Solution 2

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 

Solution 3

1. add the column

   alter table table_name add (id INTEGER);

2. create a sequence table_name_id_seq with start with clause, using number of rows in the table + 1 or another safe value(we don't want duplicate ids);

3. lock the table (no inserts)

   alter table table_name lock exclusive mode;

4. fill the column

   update table_name set id = rownum; --or another logic

5. add a trigger to automaticaly put the id on insert using the sequence(you can find examples on internet, for example this answer)

When you'll fire the create trigger the lock will be released. (it automatically commits). Also, you may add unique constraint on the id column, it is best to do so.

Comments

[Alex Poole]

The sequence needs to be created with start with matching the highest value set in step 4, rather than the default value of 1, otherwise you'll get clashes when you first insert new rows. Also might want to make it the PK, or at least make it unique?

[Alex Poole]

The sequence needs to be created with start with matching the highest value set manually from rownum, rather than the default value of 1; otherwise you'll get constraint violations when you first insert new rows.

[Florin Ghita]

ouch, you're right, i'll mention that in the answer.

[Amirhossein]

this question has a correct answer... welcome to stack pleas read help center and get tour for give best feedback : stackoverflow.com/tour