How to Add Auto-Increment to an Existing Primary Key Column in Oracle?

Adding Auto-Increment to an Existing Column in Oracle

In Oracle, to add auto-increment functionality to an existing column that is already the primary key of a table, you can employ different methods depending on the version of Oracle you are using.

For Oracle 11g and Prior

• Create a sequence to increment the column via a trigger.

CREATE SEQUENCE t_seq
  START WITH 150111111
  INCREMENT BY 1;

CREATE OR REPLACE TRIGGER t_trg
  BEFORE INSERT ON t
  FOR EACH ROW
  WHEN (new.id IS NULL)
  BEGIN
    SELECT t_seq.NEXTVAL
    INTO   :new.id
    FROM   dual;
  END;

For Oracle 12c and Later

• Use the Identity column feature.

CREATE TABLE t
  (
    ID NUMBER GENERATED ALWAYS AS IDENTITY
    START WITH 150111111 INCREMENT BY 1,
    text VARCHAR2(50)
  );

Inserting Data

Once the auto-increment functionality is configured, you can insert data into the table as usual.

Example

Consider a table named t with a column named ID as the primary key, and you want to configure it for auto-increment starting from 150111111.

Using the sequence method in Oracle 11g:

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

Using the Identity column method in Oracle 12c:

INSERT INTO t(text) VALUES('This table has an identity column');

Note:

• The sequence created for auto-incrementing the primary key is named ISEQ$$, which can be verified using the USER_SEQUENCES view.
• The ALL_TAB_IDENTITY_COLS view provides information about the identity columns in the database.

The above is the detailed content of How to Add Auto-Increment to an Existing Primary Key Column in Oracle?. For more information, please follow other related articles on the PHP Chinese website!