By Sushan Ghimire


2012-07-02 15:09:48 8 Comments

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?

16 comments

@Eugenio Cuevas 2012-07-02 15:16:05

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

@J86 2014-02-24 14:59:02

I'm a n00b, can you please tell me where dept_seq came from!

@Umingo 2014-03-14 15:52:54

CREATE SEQUENCE dept_seq; creates dept_seq ... like a table .. but in this case its only a number that you can increase with dept_seq.NEXTVAL ... see the trigger.

@FanaticD 2015-04-29 13:16:23

As was mentioned, the original code would fail when encountering a line with ID specified. But how about this case: The trigger would assign the id (automatically) only if there was no id specified explicitly in INSERT. This would fail, right? And what is the proper way to do this?

@Loic 2015-08-05 11:44:13

Is there a way to have the auto increment field begin from a defined value?

@ShirouWrath 2015-10-05 13:00:32

@Loic If you are using squences, you can use START WITH parameter, like:CREATE SEQUENCE "DB"."NewSequence" START WITH 30; I don't know if its possible to alter counter of existing sequence.

@Hoppe 2016-02-16 14:51:37

For oracle newbies like me, the 'id' part of 'new.id' refers to the column 'id' in the table above. 'new' is a reserved word referring to the new row created

@MT0 2017-05-17 09:49:18

You do not need to use SELECT .. INTO in the trigger you can just do :new.id := dept_seq.NEXTVAL;.

@Tomáลก Zato 2018-11-20 01:56:19

How would I go about using the IDENTITY(START with 1 INCREMENT by 1) in an ALTER TABLE statement?

@Nisar 2014-06-14 18:10:38

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

@Jon Heller 2014-06-15 15:48:16

This answer does not add anything not already in the accepted answer.

@EpicPandaForce 2015-03-23 13:55:47

@JonHeller I personally say the IDENTITY example is much clearer in this answer.

@dcsohl 2015-04-30 13:31:43

The WHEN (new.MAP_ID IS NULL) is not in the accepted answer. Upvoted.

@ajmalmhd04 2015-05-21 01:43:05

@dcsohl, WHEN ( new.MAP_ID is null) is not a good code in this case and is already explained in the comment section by @A.B.Cade under accepted answer.. have a read;)

@vapcguy 2016-07-19 18:43:10

When I run this from CREATE OR REPLACE TRIGGER to END;, I get an "Enter Binds" window. If I click "Apply" and don't do anything else in that window, and then run the ALTER TRIGGER command, all is good, but wish there was a way to programmatically get rid of that pop-up and run everything together. If you try it altogether, you get PLS-00103: Encountered the symbol 'ALTER' and it doesn't like EXECUTE IMMEDIATE, either (same error, just says it Encountered the symbol 'EXECUTE' instead).

@belgoros 2017-04-12 12:18:38

I got [42000][907] ORA-00907: missing right parenthesis when running the version for Oracle 12c onward. Any idea ?

@Rach Sharp 2017-09-29 09:46:31

ORA-00907 is because of the comma in START WITH 1, INCREMENT BY 1. People should use the examples in the accepted answer as this one seems to be wrong on a couple of points - I also got an error from NOT NULL GENERATED ALWAYS until I took out the NOT NULL part.

@kumar venkatesan 2017-08-02 11:25:31

FUNCTION UNIQUE2(
 seq IN NUMBER
) RETURN VARCHAR2
AS
 i NUMBER := seq;
 s VARCHAR2(9);
 r NUMBER(2,0);
BEGIN
  WHILE i > 0 LOOP
    r := MOD( i, 36 );
    i := ( i - r ) / 36;
    IF ( r < 10 ) THEN
      s := TO_CHAR(r) || s;
    ELSE
      s := CHR( 55 + r ) || s;
    END IF;
  END LOOP;
  RETURN 'ID'||LPAD( s, 14, '0' );
END;

@kumar venkatesan 2017-08-02 11:22:07

FUNCTION GETUNIQUEID_2 RETURN VARCHAR2
AS
v_curr_id NUMBER;
v_inc NUMBER;
v_next_val NUMBER;
pragma autonomous_transaction;
begin 
CREATE SEQUENCE sequnce
START WITH YYMMDD0000000001
INCREMENT BY 1
NOCACHE
select sequence.nextval into v_curr_id from dual;
if(substr(v_curr_id,0,6)= to_char(sysdate,'yymmdd')) then
v_next_val := to_number(to_char(SYSDATE+1, 'yymmdd') || '0000000000');
v_inc := v_next_val - v_curr_id;
execute immediate ' alter sequence sequence increment by ' || v_inc ;
select sequence.nextval into v_curr_id from dual;
execute immediate ' alter sequence sequence increment by 1';
else
dbms_output.put_line('exception : file not found');
end if;
RETURN 'ID'||v_curr_id;
END;

@Corrado Piola 2013-10-10 14:47:03

Oracle Database 12c introduced Identity, an auto-incremental (system-generated) column. In the previous database versions (until 11g), you usually implement an Identity by creating a Sequence and a Trigger. From 12c onward, you can create your own Table and define the column that has to be generated as an Identity.

The following article explains how to use it:

Identity columns - A new entry in Oracle Database 12c

@Bridge 2013-10-10 15:13:57

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes.

@sam 2017-03-10 12:44:26

it is called Identity Columns and it is available only from oracle Oracle 12c

CREATE TABLE identity_test_tab
(
   id            NUMBER GENERATED ALWAYS AS IDENTITY,
   description   VARCHAR2 (30)
);

example of insert into Identity Columns as below

INSERT INTO identity_test_tab (description) VALUES ('Just DESCRIPTION');

1 row created.

you can NOT do insert like below

INSERT INTO identity_test_tab (id, description) VALUES (NULL, 'ID=NULL and DESCRIPTION');

ERROR at line 1: ORA-32795: cannot insert into a generated always identity column

INSERT INTO identity_test_tab (id, description) VALUES (999, 'ID=999 and DESCRIPTION');

ERROR at line 1: ORA-32795: cannot insert into a generated always identity column

useful link

@ether6 2017-01-18 23:06:13

This is how I did this on an existing table and column (named id):

UPDATE table SET id=ROWNUM;
DECLARE
  maxval NUMBER;
BEGIN
  SELECT MAX(id) INTO maxval FROM table;
  EXECUTE IMMEDIATE 'DROP SEQUENCE table_seq';
  EXECUTE IMMEDIATE 'CREATE SEQUENCE table_seq START WITH '|| TO_CHAR(TO_NUMBER(maxval)+1) ||' INCREMENT BY 1 NOMAXVALUE';
END;
CREATE TRIGGER table_trigger
  BEFORE INSERT ON table
  FOR EACH ROW
BEGIN
  :new.id := table_seq.NEXTVAL;
END;

@Mark Harrison 2010-01-12 23:44:52

Here are three flavors:

  1. numeric. Simple increasing numeric value, e.g. 1,2,3,....
  2. GUID. globally univeral identifier, as a RAW datatype.
  3. GUID (string). Same as above, but as a string which might be easier to handle in some languages.

x is the identity column. Substitute FOO with your table name in each of the examples.

-- numerical identity, e.g. 1,2,3...
create table FOO (
    x number primary key
);
create sequence  FOO_seq;

create or replace trigger FOO_trg
before insert on FOO
for each row
begin
  select FOO_seq.nextval into :new.x from dual;
end;
/

-- GUID identity, e.g. 7CFF0C304187716EE040488AA1F9749A
-- use the commented out lines if you prefer RAW over VARCHAR2.
create table FOO (
    x varchar(32) primary key        -- string version
    -- x raw(32) primary key         -- raw version
);

create or replace trigger FOO_trg
before insert on FOO
for each row
begin
  select cast(sys_guid() as varchar2(32)) into :new.x from dual;  -- string version
  -- select sys_guid() into :new.x from dual;                     -- raw version
end;
/

update:

Oracle 12c introduces these two variants that don't depend on triggers:

create table mytable(id number default mysequence.nextval);
create table mytable(id number generated as identity);

The first one uses a sequence in the traditional way; the second manages the value internally.

@Nate Zaugg 2016-02-24 15:53:29

Starting with Oracle 12c there is support for Identity columns in one of two ways:

  1. Sequence + Table - In this solution you still create a sequence as you normally would, then you use the following DDL:

    CREATE TABLE MyTable (ID NUMBER DEFAULT MyTable_Seq.NEXTVAL, ...)

  2. Table Only - In this solution no sequence is explicitly specified. You would use the following DDL:

    CREATE TABLE MyTable (ID NUMBER GENERATED AS IDENTITY, ...)

If you use the first way it is backward compatible with the existing way of doing things. The second is a little more straightforward and is more inline with the rest of the RDMS systems out there.

@emkays 2016-02-17 11:12:47

Here is complete solution w.r.t exception/error handling for auto increment, this solution is backward compatible and will work on 11g & 12c, specifically if application is in production.

Please replace 'TABLE_NAME' with your appropriate table name

--checking if table already exisits
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE TABLE_NAME';
    EXCEPTION WHEN OTHERS THEN NULL;
END;
/

--creating table
CREATE TABLE TABLE_NAME (
       ID NUMBER(10) PRIMARY KEY NOT NULL,
       .
       .
       .
);

--checking if sequence already exists
BEGIN
    EXECUTE IMMEDIATE 'DROP SEQUENCE TABLE_NAME_SEQ';
    EXCEPTION WHEN OTHERS THEN NULL;
END;

--creating sequence
/
CREATE SEQUENCE TABLE_NAME_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 1 NOMAXVALUE NOCYCLE CACHE 2;

--granting rights as per required user group
/
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE_NAME TO USER_GROUP;

-- creating trigger
/
CREATE OR REPLACE TRIGGER TABLE_NAME_TS BEFORE INSERT OR UPDATE ON TABLE_NAME FOR EACH ROW
BEGIN    
    -- auto increment column
    SELECT TABLE_NAME_SEQ.NextVal INTO :New.ID FROM dual;

    -- You can also put some other required default data as per need of your columns, for example
    SELECT SYS_CONTEXT('USERENV', 'SESSIONID') INTO :New.SessionID FROM dual;
    SELECT SYS_CONTEXT('USERENV','SERVER_HOST') INTO :New.HostName FROM dual;
    SELECT SYS_CONTEXT('USERENV','OS_USER') INTO :New.LoginID FROM dual;    
    .
    .
    .
END;
/

@abhishek ringsia 2015-09-21 06:46:21

  create trigger t1_trigger
  before insert on AUDITLOGS
  for each row
   begin
     select t1_seq.nextval into :new.id from dual;
   end;

only I have to just change the table name (AUDITLOGS) with your table name and new.id with new.column_name

@Martin Hlavaj 2015-09-02 09:24:22

Maybe just try this simple script:

http://www.hlavaj.sk/ai.php

Result is:

CREATE SEQUENCE TABLE_PK_SEQ; 
CREATE OR REPLACE TRIGGER TR_SEQ_TABLE BEFORE INSERT ON TABLE FOR EACH ROW 

BEGIN
SELECT TABLE_PK_SEQ.NEXTVAL
INTO :new.PK
FROM dual;
END;

@a_horse_with_no_name 2015-09-02 09:46:32

How is this different than eugnio's answer? Plus: you don't need the select in modern Oracle versions. You can simply use:new.pk := TABLE_PK_SEQ.NEXTVAL

@N J 2014-01-18 06:08:37

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.

@Satyadev 2015-12-20 14:49:12

What happens when a row is deleted? Will the SYS_GUID() its id values as well?

@Justin Cave 2012-07-02 15:16:08

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

@Angelina 2013-05-29 13:33:46

What does CACHE 100; in CREATE SEQUENCE name_of_sequence START WITH 1 INCREMENT BY 1 CACHE 100; do?

@Ramazan Polat 2013-06-04 07:23:05

CACHE 100: keyword fetches next 100 numbers to memory. Normally a SEQUENCE is saved into database whenever its value changed, if you cache it, it will be saved and retrieved only if cached ones exhausted. Gives you a significant performance gain, but if database fails, you lose all cached values you didn't even use.

@turbanoff 2013-10-09 11:44:30

A SYS_GUID() is a RAW(16), not 32.

@Justin Cave 2013-10-09 12:30:22

@turbanoff - Good catch. Updated my answer. The SYS_GUID documentation declares a raw(32) which confused me.

@Satyadev 2015-12-20 15:13:42

@JustinCave I have used your approach in creating the sequence and trigger. What if I delete a row programmatically (java), will the ID(primary key) gets adjusted as well?

@Justin Cave 2015-12-20 22:44:37

@Satyadev No. I'm not sure what you would want to "adjust". A sequence generated key is not meaningful. If you want to adjust it, that implies it has meaning. So the question doesn't really make sense

@Batman 2018-02-08 00:41:21

@JustinCave If I have an existing table and want to create a new column and use that as my primary key with the default being sys_guid(), would this work: ALTER TABLE MyTable ADD PKID RAW(16) DEFAULT SYS_GUID() NOT NULL; ALTER TABLE MyTable ADD CONSTRAINT PK_LLATTRDATA PRIMARY KEY(PKID);

@Kalpesh Soni 2013-08-27 21:41:45

@ygoe 2015-08-21 08:29:43

Helpful information and interesting performance comparison. Using IDENTITY columns is better than using a TRIGGER for a SEQUENCE.

@Phil Sandler 2010-01-12 23:44:50

Assuming you mean a column like the SQL Server identity column?

In Oracle, you use a SEQUENCE to achieve the same functionality. I'll see if I can find a good link and post it here.

Update: looks like you found it yourself. Here is the link anyway: http://www.techonthenet.com/oracle/sequences.php

Related Questions

Sponsored Content

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

18 Answered Questions

32 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

22 Answered Questions

[SOLVED] How to reset AUTO_INCREMENT in MySQL?

7 Answered Questions

17 Answered Questions

[SOLVED] How can I get column names from a table in SQL Server?

14 Answered Questions

[SOLVED] How do I limit the number of rows returned by an Oracle query after ordering?

19 Answered Questions

[SOLVED] Get list of all tables in Oracle?

  • 2008-10-15 17:54:49
  • vitule
  • 2002940 View
  • 1001 Score
  • 19 Answer
  • Tags:   sql oracle

5 Answered Questions

[SOLVED] Oracle and auto_increment/identity

1 Answered Questions

[SOLVED] How to create id with AUTO_INCREMENT on a view in Oracle

  • 2016-10-27 15:59:58
  • J K
  • 486 View
  • 0 Score
  • 1 Answer
  • Tags:   oracle oracle11g

Sponsored Content