How Do I Create Auto-Incrementing Columns in Oracle Databases?

Auto-increment column in Oracle database

In Oracle Database, unlike other database systems, there was no concept of auto-incrementing columns until version 11g. This can create challenges when creating tables that require auto-incrementing identifiers. Fortunately, you can use sequences and triggers to simulate this behavior.

Simulation using sequences and triggers

In Oracle 11g and earlier, you can create sequences and triggers to simulate auto-incrementing columns. First, define a table containing a numeric column used as an identifier:

<code class="language-sql">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;</code>

Next, create a trigger that populates the ID column with the next value of the sequence when a new record is inserted:

<code class="language-sql">CREATE OR REPLACE TRIGGER dept_bir
BEFORE INSERT ON departments
FOR EACH ROW

BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;</code>

Identity Column (Oracle 12c and later)

Starting with Oracle 12c, identity columns were introduced as a true auto-increment feature. You can define an identity column in a table as follows:

<code class="language-sql">CREATE TABLE t1 (
  c1 NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  c2 VARCHAR2(10)
);</code>

Identity columns can also specify starting and incremental values ​​to prevent manual updates:

<code class="language-sql">CREATE TABLE t1 (
  c1 NUMBER GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1),
  c2 VARCHAR2(10)
);</code>

Use sequences as an alternative to default values

Oracle 12 also provides a way to use sequences as default values:

<code class="language-sql">CREATE SEQUENCE dept_seq START WITH 1;

CREATE TABLE departments (
  ID           NUMBER(10)    DEFAULT dept_seq.NEXTVAL NOT NULL,
  DESCRIPTION  VARCHAR2(50)  NOT NULL
);</code>

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