Oracle DB - Primary Key Auto Increment Column without Sequence

Question

I wonder if there is a way to create a table column with an primary key which is auto incremented without using a sequence.

I saw that it was working by using IDENTITY on Microsofts SQL Server and AUTO_INCREMENT on MySQL, but cannot get something that works with Oracle DB.

This is my current approach:

CREATE TABLE test
(   id NUMBER(6) IDENTITY,
    CONSTRAINT pk_id PRIMARY KEY (id)
)

Database version? Identity columns were introduced in Oracle 12c. Pre-12c, you're going to have to use a sequence. — Kris Johnston
Oh then that may be the Problem, i use the Express Version 11g. Would it work with 12c in that way i described? Thank you for the fast response :) — DJ MERKEL
Pretty close... the syntax is a little different though. Tim Hall has a good article on identity columns here: oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1. In any case, your choices are to either use a sequence, or to upgrade to 12c (and I don't think 12c is available yet for express edition). Tim Hall has a pretty good write up of auto_increment in pre-12c here: oracle-base.com/articles/misc/autonumber-and-identity — Kris Johnston

Kris Johnston Kris Johnston · Accepted Answer · 2016-02-03T20:23:23

Identity columns in Oracle would meet your requirement, but they were introduced in Oracle Database 12c.

Since you are on Oracle Database 11g, the best approach would be to use a sequence + trigger approach. Tim Hall has a good write up of this here:

Excerpt:

Create a table with a suitable primary key column and a sequence to support it.

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;

Create a trigger to populate the ID column if it's not specified in the insert.

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

Oracle DB - Primary Key Auto Increment Column without Sequence

1 Answers