-2

I am facing issue while adding new column as auto increment in existing table.

When I use this command:

ALTER TABLE NCS.generic_usg_data_store_dum ADD id int AUTO_INCREMENT primary key;

getting an error like below:

SQL Error: ORA-01735: invalid ALTER TABLE option
01735. 00000 -  "invalid ALTER TABLE option"

Anyone please assist me on this

MT0
  • 143,790
  • 11
  • 59
  • 117
Pooja Karanam
  • 1
  • This is a duplicate of https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle?noredirect=1&lq=1 – MT0 Jul 05 '23 at 10:47
  • [`ALTER TABLE`](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-TABLE.html) – astentx Jul 05 '23 at 10:57

1 Answers1

0

That's because you used wrong syntax for alter table.

Here's correct one:

SQL> create table test_so
  2    (name varchar2(20));

Table created.

SQL> alter table test_so add id int generated always as identity primary key;

Table altered.

SQL>

[EDIT]

As you commented that such a statement won't work, it appears that your Oracle database version doesn't support identity columns. In that case, you'll have to do that with a database trigger. Here's how:

SQL> create table test_so
  2    (name varchar2(20));

Table created.

SQL> alter table test_so add id int;

Table altered.

SQL> create sequence seq_so;

Sequence created.

SQL> create or replace trigger trg_bi_test
  2    before insert on test_so
  3    for each row
  4    when (new.id is null)
  5  begin
  6    :new.id := seq_so.nextval;
  7  end;
  8  /

Trigger created.

Testing:

SQL> insert into test_so (name) values ('Littlefoot');

1 row created.

SQL> select * from test_so;

NAME                         ID
-------------------- ----------
Littlefoot                    1

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57