Auto Incremant composite key Oracle

Question

How to have multiple sequences depending on the value of specific column in Oracle; that is, new Sequence for each different value for that column.

Table is:

  CREATE TABLE TEST 
     (
      TYPE VARCHAR2(20) NOT NULL ,
      SERIAL_NUM INT NOT NULL,
      CONSTRAINT TEST_PK PRIMARY KEY 
      (
        TYPE,
       SERIAL_NUM 
      )
      ENABLE 
    );

This Link: How to create id with AUTO_INCREMENT on Oracle?

shows how to generate auto incremental Sequence assuming that the primary key is one attribute. How to have separate Sequence for each unique value in TYPE column?

I was thinking of creating multiple Sequences for each possible value in TYPE, creating Trigger for each Sequence and adding ifcondition for flirting based on TYPE column value. But, I realized that this is not the right approach since I need to create new Sequence and Trigger for any new TYPE added.

A sample data should be similar to the following:

TYPE    SERIAL_NUM
X             1
X             2
X             3
Y             1
Y             2
Z             1
Z             2
Z             3
Z             4

Any suggestions...

What prevents you from using a single sequence for all types? — Frank Schmitt, Feb 18 '14 at 10:34
What is the business problem that you want to solve? Why do you have a requirement that there must a a row where the `serial_num` is 1 for `type` A and then another row with a `serial_num` of 1 for `type` B? What problem do you get from having the `serial_num` values be unique? Hopefully, you're not trying to create a gap-free set of values... — Justin Cave, Feb 18 '14 at 10:34
Actually, nothing prevents me to have unique serial number for each item. I was asking if that possible. Also, what make me think of that is that I have the`TYPE`column which is part of the primary key and I thought that data will be more organized and easier to go through @FrankSchmitt @JustinCave — Salman, Feb 18 '14 at 10:51

score 1 · Answer 1 · answered Feb 18 '14 at 11:45

There is no built-in functionality in Oracle for this.

Solution 1.

Create a sequence for each type. If new types can be added in run-time then you need to do DDL in run-time (using EXECUTE IMMEDIATE). If there are many types you will get many sequences.

Solution 2.

Implement your own sequence-like functionality in a separate table with one row for each type to keep track of the next value. Be sure to use SELECT FOR UPDATE and in general be mindful of concurrency issues if going with this option.

CREATE TABLE PseudoSequence (
  TYPE    VARCHAR2(20) PRIMARY KEY,
  SEQ_NO  INT NOT NULL
);

Note that option 1 is more scalable with regard to concurrent insertions of records of the same type.

Auto Incremant composite key Oracle

1 Answers1