CODEWITHSUNDEEP
sqloracle-databaseplsqlsequence
David Garcia
David Garcia

Reputation: 2696

Primary key format for oracle database

I would like to use alpha-numeric format for primary key, that is A1 A2 A3 and have it auto increment.

How can i do that? is it even recommendable?

Upvotes: 1

Views: 930

Answers (1)

OMG Ponies
OMG Ponies

Reputation: 332781

You'd need to create a SEQUENCE:

CREATE SEQUENCE your_seq
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

Then, you use:

INSERT INTO your_table
  (pk_column, ...)
SELECT 'A' || your_seq.NEXTVAL ...

If you want that alphanumeric value to increment as well, create another sequence & use the CHR function:

SELECT CHR(alpha_seq.NEXTVAL) || your_seq.NEXTVAL ...

But my recommendation would be to make this column your surrogate key, and use the sequence value for the actual primary key column:

INSERT INTO your_table
  (pk_column, surrogate_key, ...)
SELECT your_seq.NEXTVAL,
       'A' || your_seq.NEXTVAL ...

...because VARCHAR2 will take more bytes than NUMBER. It'll make a difference when JOINing tables, and allow the flexibility to change the surrogate key without impacting referential integrity (IE: B1, B2, B3...).

Upvotes: 6

Related Questions