CODEWITHSUNDEEP
mysqloracle-databaseoracle11g
DJ MERKEL
DJ MERKEL

Reputation: 87

Oracle DB - Primary Key Auto Increment Column without Sequence

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

Upvotes: 0

Views: 2569

Answers (1)

Kris Johnston
Kris Johnston

Reputation: 738

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;

Upvotes: 1

Related Questions