CODEWITHSUNDEEP
sqloracle-database
LPK
LPK

Reputation: 536

How to auto_increment a value from a column when inserting a new row

I know that it seems similar to some questions, but I hope mine is different.

I work with an Oracle Database

I want to have an auto_increment on a column by using

 COMPUTED column and LAST_VALUE(column) + 1

So I have the following request :

ALTER TABLE schema.table (

ADD SK NUMBER ALWAYS AS (LAST_VALUE(SK)+1)

);

Is it gonna do the trick with only that ?

Or do I need to add a FOR EACH ROW sentence so that fits with my need of auto_increment ?

EDIT According to G00dy's comment:

The sequence :

create sequence SK_SEQUENCES
increment by 1
start with 1
  nomaxvalue
  minvalue 1
nocycle
order
keep;

The table :

create table schema.test(
isCurrent CHAR(10),
SK NUMBER
);

If I understand the comment from @g00dy,

I need to add the Sequence as a value for my column SK,

so I have this :

insert into schema.test(SK)
values (SK_SEQUENCES.nextval)

Then ok, it works

But when I'm adding value to the isCurrent column,

there's no auto_increment on the SK column

I guess, to have the auto_increment I need to create a trigger.

Maybe I'll have to use trigger/sequence in order to fix my issue but I don't want to..

Upvotes: 1

Views: 761

Answers (1)

William Robertson
William Robertson

Reputation: 15991

No, it won't work.

Firstly, the syntax is generated always, not just always, and there are no brackets around the add clause. However, this still won't work:

alter table demo
add sk integer generated always as (last_value(sk)+1);

fails with:

ORA-30484: missing window specification for this function

because last_value is an analytic function that needs to be part of a query and have a window specification like over (partition by xxx order by yyy). You can't use an analytic function as a column default.

From Oracle 12.1 you can define an identity column as:

alter table demo
add sk integer generated always as identity;

In earlier Oracle versions you would need to either specify the sequence.nextval when inserting, or else create a trigger as

create sequence sk_seq;

create or replace trigger demo_generate_sk_trg
before insert on demo for each row
begin
    :new.dummy := sk_seq.nextval;
end;
/

Upvotes: 2

Related Questions