Oracle: how to create an identity column? [duplicate]

Question

It appears that there is no concept of AUTO_INCREMENT in Oracle, up until and including version 11g.

How can I create a column that behaves like auto increment in Oracle 11g?

Answer 1

There is no such thing as "auto_increment" or "identity" columns in Oracle as of Oracle 11g. However, you can model it easily with a sequence and a trigger:

Table definition:

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; 

Trigger definition:

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

UPDATE:

IDENTITY column is now available on Oracle 12c:

create table t1 (     c1 NUMBER GENERATED by default on null as IDENTITY,     c2 VARCHAR2(10)     ); 

or specify starting and increment values, also preventing any insert into the identity column (GENERATED ALWAYS) (again, Oracle 12c+ only)

create table t1 (     c1 NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),     c2 VARCHAR2(10)     ); 

Alternatively, Oracle 12 also allows to use a sequence as a default value:

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

Answer 2

SYS_GUID returns a GUID-- a globally unique ID. A SYS_GUID is a RAW(16). It does not generate an incrementing numeric value.

If you want to create an incrementing numeric key, you'll want to create a sequence.

CREATE SEQUENCE name_of_sequence
  START WITH 1
  INCREMENT BY 1
  CACHE 100;

You would then either use that sequence in your INSERT statement

INSERT INTO name_of_table( primary_key_column, <<other columns>> )
  VALUES( name_of_sequence.nextval, <<other values>> );

Or you can define a trigger that automatically populates the primary key value using the sequence

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  SELECT name_of_sequence.nextval
    INTO :new.primary_key_column
    FROM dual;
END;

If you are using Oracle 11.1 or later, you can simplify the trigger a bit

CREATE OR REPLACE TRIGGER trigger_name
  BEFORE INSERT ON table_name
  FOR EACH ROW
BEGIN
  :new.primary_key_column := name_of_sequence.nextval;
END;

If you really want to use SYS_GUID

CREATE TABLE table_name (
  primary_key_column raw(16) default sys_guid() primary key,
  <<other columns>>
)