Oracle Primary Key AUTO-INCREMENT

Question

If I want to create a table on Oracle SQLDeveloper and put in the primary key a varchar which always starts with "c" followed by 9 digits which auto-increment, how could I?

Example: C0000000001

Why would you want to do that? Create an `identity` column and format the ID with the leading `C` and zeros when you _display_ that number. — a_horse_with_no_name, Oct 19 '21 at 14:38

score 0 · Answer 1 · answered Oct 19 '21 at 14:29

0

You could do this:

create table test_table (
    id                             VARCHAR2(30) 
                                   constraint test_table_id_pk primary key
)
;


-- triggers
create or replace trigger test_table_biu
    before insert
    on test_table
    for each row
begin
    :id := 'c'||LPAD(test_table_seq.NEXTVAL,9,0);
end test_table_biu;
/

Note that it will start failing when the sequence reaches a 10 digit value because of duplicate values (it will cut off the extra digits).

answered Oct 19 '21 at 14:29

Koen Lostrie

9,513
2
9
17

my answer : CREATE SEQUENCE sequence MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE ; rpad('C',10-length(sequence.nextval),'0')||sequence.nextval – DavidZrb Oct 20 '21 at 15:09

Oracle Primary Key AUTO-INCREMENT

1 Answers1