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