How to Create Auto-Incrementing Columns in Oracle: 11g vs. 12c and Beyond?

Creating Auto-Increment Columns in Oracle 11g and Beyond

Oracle databases lack the concept of auto-increment columns until version 12c. Despite this, it's possible to emulate this behavior in Oracle 11g using a sequence and a trigger.

Method for Oracle 11g

Begin by defining the table with a primary key column:

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;

Next, create a trigger that generates unique ID values for new insertions:

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW

BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;

Update for Oracle 12c and Later

Oracle 12c introduces a native IDENTITY column type, providing a true auto-increment feature:

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

Alternatively, you can specify custom starting and increment values:

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

Or, in Oracle 12c and higher, use a sequence as the default value:

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

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