How to Add Auto-Incrementing Functionality to Existing Oracle Table Columns?

Auto-Incrementing Existing Oracle Table Columns

In Oracle, adding auto-increment functionality to an existing column with a primary key can be achieved in different ways depending on the Oracle version.

Oracle 11g and Prior

Create a sequence that will increment the column's values:

CREATE SEQUENCE t_seq
START WITH 150111111
INCREMENT BY 1;

Use a trigger to assign the next sequence value to the column during inserts:

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;

Oracle 12c and Later

Oracle 12c introduced the Identity column, which allows auto-incrementing values without the need for sequences or triggers:

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

Once the column is in place, new rows will be auto-incremented starting from the specified starting value.

Example Using Oracle 11g

Given the table:

TABLE t (
 SEQ_ID NUMBER(10) NOT NULL
);

After creating the sequence "t_seq" and the trigger "t_trg", insert new rows:

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

Query the table to verify the auto-incrementing:

SELECT * FROM t;

Example Using Oracle 12c

With the table defined as:

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

Insert new rows:

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

Query the table for confirmation:

SELECT * FROM t;

In both Oracle versions, the column "ID" will now be auto-incremented starting from the specified starting value (150111111) with a step of 1.

The above is the detailed content of How to Add Auto-Incrementing Functionality to Existing Oracle Table Columns?. For more information, please follow other related articles on the PHP Chinese website!