Custom generated Identity column in Oracle-CodePudding

I read the documentation for Identity columns in Oracle as detailed here: https://docs.oracle.com/en/database/other-databases/nosql-database/19.1/java-driver-table/creating-tables-identity-column.html

The documentation mentions that the values that get inserted by the sequence generator into the identity column are integers. This can be found here https://docs.oracle.com/en/database/other-databases/nosql-database/19.1/java-driver-table/sequence-generator-attributes.html

Now, my question is this: Is there a way to have custom values generated for the identity column? Other than integer values. Say, a string like 'ID001', 'ID002' etc?
Many thanks in advance

CodePudding user response：

First think why should you use strings as a primary key.

Do you see some advantage? (Other than using more storage?)

Secondly with your key such as ID001 consider what should happen if you have more than 1000 rows...

One explanation is that you are looking for some user readable identification of the row. This can be provided using the standard IDENTITY plus an additional virtual column

Example

create table MY_TABLE (
ID number(19,0) generated as identity, 
ID_DESC varchar2(255 char) generated always as ('ID'||ID) virtual,
col int,
primary key (ID))
;

insert into my_table(col)
select rownum from dual connect by level <= 3;


select id, id_desc from  my_table order by id;

        ID ID_DESC
---------- ---------        
         1 ID1                                                                                                                                                                                                                                                            
         2 ID2                                                                                                                                                                                                                                                            
         3 ID3

CodePudding user response：

It is possible, but you'll have to do something about it (i.e. write some code - a trigger). Here's an example.

Sample table; its ID column should be auto-generated:

SQL> create table test (id varchar2(10), name varchar2(10));

Table created.

A sequence, which will be used in a trigger:

SQL> create sequence seq_test;

Sequence created.

Trigger is rather simple; it concatenates ID with a sequence number, left-padded with zeroes up to 3 characters in length (as your example says so):

SQL> create or replace trigger trg_bi_test
  2    before insert on test
  3    for each row
  4  begin
  5    :new.id := 'ID' || lpad(seq_test.nextval, 3, '0');
  6  end;
  7  /

Trigger created.

Testing:

SQL> insert into test (name) values ('Little');

1 row created.

SQL> insert into test (name)
  2    select 'Foot'      from dual union all
  3    select 'Equalizer' from dual;

2 rows created.

SQL> select * from test order by id;

ID         NAME
---------- ----------
ID001      Little
ID002      Foot
ID003      Equalizer

SQL>

P.S. Link you posted isn't related to "traditional" Oracle database; check e.g. CREATE TABLE documentation, it contains info about identity columns. Your link is related to NoSQL database.