How to create a unique id for an existing table in PL SQL?

Question

The situation is that, when I import a file into the database, one of the first thing I usually do is to assign an unique ID for each record.

I normally do below in TSQL

ALTER TABLE MyTable
    ADD ID INT IDENTITY(1,1)

I am wondering if there is something similar in PL SQL?

All my search result come back with multiple steps.

Then I'd like to know what PL SQL programmer typically do to ID records after importing a file. Do they do that? The main purpose for me to ID these records is to trace it back after manipulation/copying.

Again, I understand there is solution there, my further question is whether PL SQL programmer actually do that, or there is other alternative which making this step not necessary in PL SQL?

Possible duplicate of [Add a auto increment primary key to existing table in oracle](https://stackoverflow.com/questions/11464396/add-a-auto-increment-primary-key-to-existing-table-in-oracle) — Zohar Peled, Oct 13 '18 at 11:31
It's a duplicate of a duplicate. According to the post I've linked to, and the one it's linked to as a duplicate, there is no auto-increment column in oracle. — Zohar Peled, Oct 13 '18 at 11:32
Many answers start with creating a table, but my scenario starts with an existing table. — Colin Xu, Oct 13 '18 at 11:44
@ZoharPeled: Oracle supports auto-incrementing (aka "identity") columns since 12.1 — , Oct 13 '18 at 16:10
@a_horse_with_no_name Thanks for the info. Not sure it's relevant to this question though, since the OP stated they work with 11... — Zohar Peled, Oct 13 '18 at 16:46
How are you importing this file? If you're using SQL\*Loader you should check out the [`recnum` feature](https://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_field_list.htm#SUTIL1239). — APC, Oct 13 '18 at 17:49

score 3 · Answer 1 · answered Oct 13 '18 at 15:43

OK then, as you're on Oracle 11g, there's no identity column there so - back to multiple steps. Here's an example:

I'm creating a table that simulates your imported table:

SQL> create table tab_import as
  2    select ename, job, sal
  3    from emp
  4    where deptno = 10;

Table created.

Add the ID column:

SQL> alter table tab_import add id number;

Table altered.

Create a sequence which will be used to populate the ID column:

SQL> create sequence seq_imp;

Sequence created.

Update current rows:

SQL> update tab_import set
  2    id = seq_imp.nextval;

3 rows updated.

Create a trigger which will take care about future inserts (if any):

SQL> create or replace trigger trg_bi_imp
  2    before insert on tab_import
  3    for each row
  4  begin
  5    :new.id := seq_imp.nextval;
  6  end;
  7  /

Trigger created.

Check what's in the table at the moment:

SQL> select * from tab_import;

ENAME      JOB              SAL         ID
---------- --------- ---------- ----------
CLARK      MANAGER         2450          1
KING       PRESIDENT       5000          2
MILLER     CLERK           1300          3

Let's import some more rows:

SQL> insert into tab_import (ename, job, sal)
  2    select ename, job, sal
  3    from emp
  4    where deptno = 20;

3 rows created.

The trigger had silently populated the ID column:

SQL> select * From tab_import;

ENAME      JOB              SAL         ID
---------- --------- ---------- ----------
CLARK      MANAGER         2450          1
KING       PRESIDENT       5000          2
MILLER     CLERK           1300          3
SMITH      CLERK            800          4
JONES      MANAGER         2975          5
FORD       ANALYST         3000          6

6 rows selected.

SQL>

Shortly: you need to

alter table and add the ID column
create a sequence
create a trigger

The end.

This looks like a standard solution. Thanks for the example, and summary at the end. — Colin Xu, Oct 19 '18 at 03:22

score 0 · Accepted Answer · answered Oct 13 '18 at 17:41

The answer given by @Littlefoot would be my recommendation too - but still I thought I could mention the following variant which will work only if you do not intend to add more rows to the table later.

ALTER TABLE MyTable add id number(38,0); 
update MyTable set id = rownum;
commit;

My test:

SQL> create table tst as select * from all_tables; 

Table created.

SQL> alter table tst add id number(38,0);  

Table altered.

SQL> update tst set id = rownum; 

3815 rows updated.

SQL> alter table tst add constraint tstPk primary key (id); 

Table altered.
SQL> 
SQL> select id from tst where id < 15; 

    ID
----------
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11

    ID
----------
    12
    13
    14

14 rows selected.

But as mentioned initially,- this only fixes numbering for the rows you have at the time of the update - your'e not going to get new id values for new rows anytime later - if you need that, go for the sequence solution.

This is what I am trying to find. I have no intention to insert further records on these tables. Once they are imported, my main task is to append further attributes on each row, and sometimes copy it out into a separated table for other processing. That's why the ID process is important to me for tracing the source. Thank you!! — Colin Xu, Oct 15 '18 at 11:16

score 0 · Answer 3 · answered Oct 14 '18 at 08:43

You can add an id column to a table with a single statement (Oracle 11g, see dbfiddle):

alter table test_
add id raw( 16 ) default sys_guid() ;

Example:

-- create a table without an id column
create table test_ ( str )
as
select dbms_random.string( 'x', 16 )
from dual
connect by level <= 10 ;

select * from test_ ;

STR
ULWL9EXFG6CIO72Z
QOM0W1R9IJ2ZD3DW
YQWAP4HZNQ57C2UH
EETF2AXD4ZKNIBBF
W9SECJYDER793MQW

alter table test_
add id raw( 16 ) default sys_guid() ;

select * from test_ ;

STR                     ID
ULWL9EXFG6CIO72Z    0x782C6EBCAE2D7B9FE050A00A02005D65
QOM0W1R9IJ2ZD3DW    0x782C6EBCAE2E7B9FE050A00A02005D65
YQWAP4HZNQ57C2UH    0x782C6EBCAE2F7B9FE050A00A02005D65
EETF2AXD4ZKNIBBF    0x782C6EBCAE307B9FE050A00A02005D65
W9SECJYDER793MQW    0x782C6EBCAE317B9FE050A00A02005D65

Testing

-- Are the id values unique and not null? Yes. 
alter table test_ 
add constraint pkey_test_ primary key ( id ) ;

-- When we insert more rows, will the id be generated? Yes.
begin 
  for i in 1 .. 100
  loop
    insert into test_ (str) values ( 'str' || to_char( i ) ) ;
  end loop ;
end ;
/

select * from test_ order by id desc ;
-- last 10 rows of the result
STR ID
str100  0x782C806E16A5E998E050A00A02005D81
str99   0x782C806E16A4E998E050A00A02005D81
str98   0x782C806E16A3E998E050A00A02005D81
str97   0x782C806E16A2E998E050A00A02005D81
str96   0x782C806E16A1E998E050A00A02005D81
str95   0x782C806E16A0E998E050A00A02005D81
str94   0x782C806E169FE998E050A00A02005D81
str93   0x782C806E169EE998E050A00A02005D81
str92   0x782C806E169DE998E050A00A02005D81
str91   0x782C806E169CE998E050A00A02005D81

Regarding your other questions:

{1} Then I'd like to know what PL SQL programmer typically do to ID records after importing a file. Do they do that? The main purpose for me to ID these records is to trace it back after manipulation/copying.

-> As you know, the purpose of an id is: to identify a row. We don't "do anything to IDs". Thus, your usage of IDs seems legit.

{2} Again, I understand there is solution there, my further question is whether PL SQL programmer actually do that, or there is other alternative which making this step not necessary in PL SQL?

-> Not quite sure what you are asking here. Although there is a ROWID() pseudocolumn (see documentation), we should not use it to identify rows.

"You should not use ROWID as the primary key of a table. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later."

How to create a unique id for an existing table in PL SQL?

3 Answers3