How Can I Add Auto-Increment Functionality to Existing Oracle Columns?

Auto-Increment for Pre-existing Oracle Columns

While adding auto-increment functionality to an existing column is not natively supported in Oracle, various approaches can be employed to achieve this.

11g and Prior: Sequence and Trigger

For Oracle versions 11g and earlier, a sequence and a trigger can be used:

• Create a sequence: This sequence will generate the auto-incrementing values.
• Alter the table: Add a primary key constraint to the column.
• Create a trigger: The trigger will insert the next value from the sequence into the column whenever a new row is inserted (if the column is null).

Example:

-- Create sequence
CREATE SEQUENCE t_seq START WITH 150111111 INCREMENT BY 1;

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

-- 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;

12c and Later: Identity Column

Oracle 12c introduced the identity column feature, which allows for auto-incrementing within the table itself:

• Create a table with an identity column: Specify the GENERATED ALWAYS AS IDENTITY attribute for the column.

• Example:

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

  Copy after login

Please note that these approaches do not update existing values in the column. To modify existing values, a separate update query would be necessary.

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