CODEWITHSUNDEEP
oracle-databasecycleidentity-column
Oguen
Oguen

Reputation: 519

Oracle Identity Column cycling

Can I define this IDENT_COL to start over from minimum value by the values of other column(s)?

i.e. If tx_ref is the same, then increase IDENT_COL.currentval by 1, else start from 1.

CREATE TABLE ogun_test(
    col_a       VARCHAR2(10),
    col_b       VARCHAR2(10),
    tx_ref      VARCHAR2(20),
    ident_col   NUMBER(*,0)
                            GENERATED BY DEFAULT AS IDENTITY
)

Upvotes: 0

Views: 187

Answers (1)

Chris Saxon
Chris Saxon

Reputation: 9865

You can define an identity that cycles: 

create table t (
  c1 int
    generated as identity
    start with 1 cycle maxvalue 2
    nocache,
  c2 int
);

insert into t values ( default, 0 );
insert into t values ( default, 0 );
insert into t values ( default, 0 );

select * from t;

C1     C2   
    1     0 
    2     0 
    1     0

But not in terms of other columns. So you'll have to use some other solution.

Upvotes: 1

Related Questions