Autoincrement in Oracle to Already Created Table

How to create id with AUTO_INCREMENT on Oracle?

There is no such thing as "auto_increment" or "identity" columns in Oracle as of Oracle 11g. However, you can model it easily with a sequence and a trigger:

Table definition:

CREATE TABLE departments (
  ID           NUMBER(10)    NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));

CREATE SEQUENCE dept_seq START WITH 1;

Trigger definition:

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW

BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

UPDATE:

IDENTITY column is now available on Oracle 12c:

create table t1 (
    c1 NUMBER GENERATED by default on null as IDENTITY,
    c2 VARCHAR2(10)
    );

or specify starting and increment values, also preventing any insert into the identity column (GENERATED ALWAYS) (again, Oracle 12c+ only)

create table t1 (
    c1 NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
    c2 VARCHAR2(10)
    );

Alternatively, Oracle 12 also allows to use a sequence as a default value:

CREATE SEQUENCE dept_seq START WITH 1;

CREATE TABLE departments (
  ID           NUMBER(10)    DEFAULT dept_seq.nextval NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID));

Autoincrement in oracle to already created table

On 11g and prior, create a sequence to increment the column via trigger. See Auto-increment primary key in Pre 12c releases (Identity functionality)

For example,

TABLE

SQL> CREATE TABLE t (
  2    ID           NUMBER(10)    NOT NULL,
  3    text  VARCHAR2(50)  NOT NULL);

Table created.

PRIMARY KEY to be populated by the sequence

SQL> ALTER TABLE t ADD (
  2    CONSTRAINT id_pk PRIMARY KEY (ID));

Table altered.

SEQUENCE to support the primary key

SQL> CREATE SEQUENCE t_seq
  2  START WITH 150111111
  3  INCREMENT BY 1;

Sequence created.

TRIGGER If you do not want to have the sequence in the INSERT , you could automate it via TRIGGER.

SQL> CREATE OR REPLACE TRIGGER t_trg
  2  BEFORE INSERT ON t
  3  FOR EACH ROW
  4  WHEN (new.id IS NULL)
  5  BEGIN
  6    SELECT t_seq.NEXTVAL
  7    INTO   :new.id
  8    FROM   dual;
  9  END;
 10  /

Trigger created.

INSERT

SQL> INSERT INTO t(text) VALUES('auto-increment test 1');

1 row created.

SQL> INSERT INTO t(text) VALUES('auto-increment test 2');

1 row created.

Let's see if we have the ID column auto-incremented with the desired values-

SQL> SELECT * FROM t;

        ID TEXT
---------- --------------------------------------------------
 150111111 auto-increment test 1
 150111112 auto-increment test 2

SQL>

So, the ID column now starts with value 150111111 and increments by 1 with subsequent inserts.

On 12c , use Identity column. See IDENTITY column autoincrement functionality in Oracle 12c

For example,

TABLE with IDENTITY COLUMN

SQL> CREATE TABLE t
  2    (
  3      ID NUMBER GENERATED ALWAYS AS IDENTITY
  4      START WITH 150111111 INCREMENT BY 1,
  5      text VARCHAR2(50)
  6    );

Table created.

INSERT

SQL> INSERT INTO t
  2    ( text
  3    ) VALUES
  4    ( 'This table has an identity column'
  5    );

1 row created.

Let's see if we have the ID column auto-incremented with the desired values-

SQL> COLUMN text format A40
SQL> SELECT * FROM t;

        ID TEXT
---------- ----------------------------------------
 150111111 This table has an identity column

So, the ID column now starts with value 150111111 and increments by 1 with subsequent inserts.

Oracle creates a sequence to populate the identity column. You can find it named as ISEQ$$

SQL> SELECT sequence_name,
  2    min_value,
  3    max_value,
  4    increment_by
  5  FROM user_sequences;

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY
------------------------------ ---------- ---------- ------------
ISEQ$$_94087                            1 1.0000E+28            1

SQL>

More information about the identity columns, use the ALL_TAB_IDENTITY_COLS view.

SQL> SELECT table_name,
  2    column_name,
  3    generation_type,
  4    identity_options
  5  FROM all_tab_identity_cols
  6  WHERE owner = 'LALIT'
  7  ORDER BY 1,
  8    2;

TABLE_NAME           COLUMN_NAME GENERATION IDENTITY_OPTIONS
-------------------- ----------- ---------- ----------------------------------------------

T                    ID          ALWAYS     START WITH: 150111111, INCREMENT BY: 1, 
                                            MAX_VALUE:9999999999999999999999999999, 
                                            MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, 
                                            ORDER_FLAG: N

How add autoincrement to existing table in Oracle

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>

Add a auto increment primary key to existing table in oracle

Say your table is called t1 and your primary-key is called id

First, create the sequence:

create sequence t1_seq start with 1 increment by 1 nomaxvalue;

Then create a trigger that increments upon insert:

create trigger t1_trigger
before insert on t1
for each row
   begin
     select t1_seq.nextval into :new.id from dual;
   end;

Adding auto increment identity to existing table in oracle which is not empty

You can not do it in one step. Instead,

Alter the table and add the column (without primary key constraint)
```
ALTER TABLE DEGREE ADD (Ident NUMBER(10));
```
Fill the new column with data which will fulfill the primary key constraint (unique/not null), e.g. like
```
UPDATE DEGREE SET Ident=ROWNUM;
```
Alter the table and add the constraint to the column
```
ALTER TABLE DEGREE MODIFY (Ident PRIMARY KEY);
```

After that is done, you can set up a SEQUENCE and a BEFORE INSERT trigger to automatically set the id value for new records.

Oracle 11g: add a column to an EXISTING table with auto increment long value

After you created the sequence, just update the existing rows:

alter table name add (id integer);

create sequence name_seq;

update name 
  set id = name_seq.nextval;
commit;

There is no need for PL/SQL or a slow and in-efficient row-by-row processing in a LOOP.

Unrelated, but: the assignment in the trigger can be simplified to:

:new.id := name_seq.NEXTVAL;

No need for a select .. from dual

How to add an auto increment primary key to a table already loaded with data in Oracle?

You can take advantage of the identity column in this case as follows:

alter table test 
add col1 number generated always as  identity (start with 1 increment by 1)

Db<>fiddle demo

It will automatically assign the sequence number to the already existing rows and will give number in sequence to new inserts also.

How to modify column to auto increment in PL SQL Developer?

This is not possible.

Oracle 10g didn't even have identity columns, they were introduced in Oracle 12.1

But even with a current Oracle version, you can't convert a regular column to an identity column. You would need to add a new one.

Before identity columns, the usual way was to create a sequence and a trigger to populate the column.

See here: How to create id with AUTO_INCREMENT on Oracle?