How to create id with AUTO_INCREMENT on Oracle?

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 table

CREATE TABLE MAPS

(

  MAP_ID INTEGER NOT NULL ,

  MAP_NAME VARCHAR(24) NOT NULL,

  UNIQUE (MAP_ID, MAP_NAME)

);

-- create sequence

CREATE SEQUENCE MAPS_SEQ;



-- create tigger using the sequence

CREATE OR REPLACE TRIGGER MAPS_TRG 

BEFORE INSERT ON MAPS 

FOR EACH ROW

WHEN (new.MAP_ID IS NULL)

BEGIN

  SELECT MAPS_SEQ.NEXTVAL

  INTO   :new.MAP_ID

  FROM   dual;

END;

/

4 years ago

Trigger and Sequence can be used when you want serialized number that anyone can easily read/remember/understand. But if you don't want to manage ID Column (like emp_id) by this way, and value of this column is not much considerable, you can use SYS_GUID() at Table Creation to get Auto Increment like this.

CREATE TABLE <table_name> 

(emp_id RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,

name VARCHAR2(30));

Now your emp_id column will accept "globally unique identifier value". you can insert value in table by ignoring emp_id column like this.

INSERT INTO <table_name> (name) VALUES ('name value');

So, it will insert unique value to your emp_id Column.

4 years ago