CODEWITHSUNDEEP
oracle-databaseoracle12cauto-increment
Ytug Ilya
Ytug Ilya

Reputation: 103

How add autoincrement to existing table in Oracle

Is there any ways to add autoincrement to primary key in already existing table in Oracle 12c. May be with ALTER TABLE function or smth, I mean without triggers and sequences.

Upvotes: 2

Views: 1275

Answers (1)

Littlefoot
Littlefoot

Reputation: 143063

As far as I can tell, you can not "modify" existing primary key column into a "real" identity column.

If you want to do that, you'll have to drop the current primary key column and then alter table and add a new identity column.

Workaround is to use a sequence (or a trigger), but - you said you don't want to do that. Anyway, if you decide to use it:

SQL> create table test
  2   (id   number constraint pk_test primary key,
  3    name varchar2(10));

Table created.

SQL> insert into test values (1, 'LF');

1 row created.

SQL> create sequence seq_test start with 2;

Sequence created.

SQL> alter table test modify id default seq_test.nextval;

Table altered.

SQL> insert into test (name) values ('BF');

1 row created.

SQL> select * from test;

        ID NAME
---------- ----------
         1 LF
         2 BF

SQL>

Or, with dropping current primary key column (note that it won't work easy if there are foreign keys involved):

SQL> alter table test drop column id;

Table altered.

SQL> alter table test add id number generated always as identity;

Table altered.

SQL> select * From test;

NAME               ID
---------- ----------
LF                  1
BF                  2

SQL> insert into test (name) values ('test');

1 row created.

SQL> select * From test;

NAME               ID
---------- ----------
LF                  1
BF                  2
test                3

SQL>

Upvotes: 3

Related Questions