Autoincrement in oracle to already created table

How to add auto-increment to the existing column in oracle? Column has already been created and it is the primary key of the table. Just want to alter table to be autoincrement. Below is the column details

 Column Name         DATA_TYPE              NULLABLE
 SEQ_ID             VARCHAR2(9 BYTE)        No  

Autoincrement number should be starting from 150111111 to the values such as 150111112, 150111113 etc

Thanks

Solution 1:

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