How to Create Auto-Increment Columns in Oracle Databases?

Auto-Incrementing Columns in Oracle: A Comprehensive Guide

Oracle databases, prior to version 12c, didn't offer native auto-increment columns. However, you can effectively replicate this functionality using sequences and triggers. Oracle 12c and later versions introduce true identity columns, offering a more streamlined approach.

Pre-12c: Simulating Auto-Increment with Sequences and Triggers

Let's begin by creating a table needing an auto-incrementing ID:

<code class="language-sql">CREATE TABLE departments (
  ID NUMBER(10) NOT NULL,
  DESCRIPTION VARCHAR2(50) NOT NULL
);</code>

Next, create a sequence to generate unique ID values:

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

Finally, a trigger ensures the sequence value is automatically assigned to the ID column upon insertion:

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

Oracle 12c and Beyond: Identity Columns

Oracle 12c introduced native identity columns, simplifying the process significantly:

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

Advanced Options in Oracle 12c

Oracle 12c offers further customization:

• Controlling Starting and Increment Values: Prevent manual insertion into the identity column by specifying start and increment values:

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

• Using Sequences with Identity Columns: You can still leverage sequences as the default value source:

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

These methods provide efficient and flexible solutions for managing auto-incrementing columns in Oracle databases, regardless of the version.

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