How to create id with AUTO_INCREMENT on Oracle?

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

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

In Oracle 12c onward you could do something like,

CREATE TABLE MAPS(  MAP_ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL,  MAP_NAME VARCHAR(24) NOT NULL,  UNIQUE (MAP_ID, MAP_NAME));

And in Oracle (Pre 12c).

-- create tableCREATE TABLE MAPS(  MAP_ID INTEGER NOT NULL ,  MAP_NAME VARCHAR(24) NOT NULL,  UNIQUE (MAP_ID, MAP_NAME));-- create sequenceCREATE SEQUENCE MAPS_SEQ;-- create tigger using the sequenceCREATE OR REPLACE TRIGGER MAPS_TRG BEFORE INSERT ON MAPS FOR EACH ROWWHEN (new.MAP_ID IS NULL)BEGIN  SELECT MAPS_SEQ.NEXTVAL  INTO   :new.MAP_ID  FROM   dual;END;/