0

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

MT0
  • 143,790
  • 11
  • 59
  • 117
DavidZrb
  • 1
  • 3
  • 1
    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. –  Oct 19 '21 at 14:38

1 Answers1

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).

Koen Lostrie
  • 14,938
  • 2
  • 13
  • 19
  • 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