How Can I Add Auto-Incrementing Functionality to Existing Columns in Oracle Databases?

Auto-Incrementing Existing Columns in Oracle Databases

Adding auto-increment functionality to an existing column in Oracle can be achieved through different approaches depending on the Oracle version.

Oracle 12c and Above: Using Identity Columns

Oracle 12c introduced the IDENTITY column feature that simplifies auto-incrementing:

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

Oracle 11g and Prior: Sequence and Trigger

For Oracle versions prior to 12c, you can use a combination of sequence and trigger:

1. Create a Sequence:

CREATE SEQUENCE t_seq START WITH 150111111 INCREMENT BY 1;

2. Alter Table and Add Primary Key:

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

3. Create Trigger:

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;

This trigger automatically populates the ID column with values from the sequence.

Example Usage:

For both methods, inserts will populate the ID column with auto-incrementing values starting from 150111111.

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

Querying the table confirms the auto-incremented IDs:

SELECT * FROM t;

ID                        TEXT
------------------------- ----------------------------------------
150111111 This table has an auto-incrementing column

Note: Oracle implicitly creates a sequence named ISEQ$$ when using identity columns.

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