CODEWITHSUNDEEP
sqloracle-databasetriggerssequenceauto-generate
Salman
Salman

Reputation: 1262

Auto Incremant composite key Oracle

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

Upvotes: 0

Views: 919

Answers (1)

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

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.

Upvotes: 1

Related Questions