How to Create Auto-Incrementing Columns in Oracle 11g and Later?

Creating AUTO_INCREMENT-like Columns in Oracle 11g

In Oracle versions up to 11g, the AUTO_INCREMENT column type is not natively available. However, there are alternative mechanisms to achieve similar functionality.

Using Sequences and Triggers

The most common approach in Oracle 11g is to use a combination of a sequence and a trigger:

Table Definition:

CREATE TABLE departments (
  ID           NUMBER(10)    NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL
);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID)
);

CREATE SEQUENCE dept_seq START WITH 1;

Trigger Definition:

CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW
BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

This trigger assigns a unique, incremental value to the newly inserted rows, mimicking the behavior of auto-increment columns.

IDENTITY Columns (Oracle 12c )

In Oracle 12c and later, the IDENTITY column type was introduced, providing a true auto-increment feature:

Table Definition:

create table t1 (
    c1 NUMBER GENERATED by default on null as IDENTITY,
    c2 VARCHAR2(10)
);

This definition creates a column 'c1' that automatically increments for each new row inserted into the table.

Alternatively, you can specify starting and increment values:

create table t1 (
    c1 NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
    c2 VARCHAR2(10)
);

Using Sequences as Default Values (Oracle 12 )

Another option in Oracle 12 and later is to use a sequence as the default value for a column:

CREATE SEQUENCE dept_seq START WITH 1;

CREATE TABLE departments (
  ID           NUMBER(10)    DEFAULT dept_seq.nextval NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL
);

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID)
);

This approach assigns incremental values using a sequence, but the actual increment operation is managed by the database.

The above is the detailed content of How to Create Auto-Incrementing Columns in Oracle 11g and Later?. For more information, please follow other related articles on the PHP Chinese website!