How to Create Auto-Increment Columns in Oracle?

Create auto-increment column in Oracle database

In Oracle versions prior to 12c, there was no concept of auto-incrementing columns. However, there are some ways we can achieve similar functionality.

Method 1: Use sequences and triggers

One way is to create a sequence and a trigger that increments the value before inserting the record.

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

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

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

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>

Method 2: Use IDENTITY column (Oracle 12c and above)

Oracle 12c introduces the IDENTITY column data type, which can automatically generate unique values.

<code class="language-sql">CREATE TABLE t1 (
    c1 NUMBER GENERATED by default on null as IDENTITY,
    c2 VARCHAR2(10)
);</code>

Method 3: Use sequence as default value (Oracle 12c and above)

Another approach is to use a sequence as the default value for the column.

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

ALTER TABLE departments ADD (
  CONSTRAINT dept_pk PRIMARY KEY (ID)
);</code>

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