How Can I Make an Existing Oracle Primary Key Column Auto-Increment?

Auto-Increment Transformation for Existing Oracle Table Columns

Issue:

How can an existing column, already designated as the primary key, be configured to auto-increment in an Oracle database? The column has been created with the data type VARCHAR2(9 BYTE).

Solution:

Oracle 11g and Prior (Pre-12c): Sequence and Trigger

1. Create a Sequence: Establish a sequence, like t_seq, with the starting value of 150111111 and incremental steps of 1.
2. Implement a Trigger: Develop a BEFORE INSERT trigger that populates the column with the next value from the sequence when the column value is NULL.

Oracle 12c and Later: Identity Column

1. Utilize Identity Column: Define an IDENTITY COLUMN in the table creation statement, starting with 150111111 and incrementing by 1.

Example:

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

Benefits of Identity Columns:

• Simplifies auto-increment implementation without the need for sequences or triggers.
• Ensures unique, sequential values for the specified column.

Additional Information:

• Oracle generates a sequence (ISEQ$$) to support the identity column.
• Monitor auto-increment information using the ALL_TAB_IDENTITY_COLS view.

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