How to create id with AUTO INCREMENT on Oracle

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

User · Answer

FUNCTION UNIQUE2   seq IN NUMBER   RETURN VARCHAR2 AS  i NUMBER    seq   s VARCHAR2 9    r NUMBER 2 0   BEGIN   WHILE i  gt  0 LOOP     r    MOD  i  36        i      i - r     36      IF   r  lt  10   THEN       s    TO CHAR r     s      ELSE       s    CHR  55   r      s      END IF    END LOOP    RETURN  ID   LPAD  s  14   0     END

User · Answer

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   lt  lt other columns gt  gt      VALUES  name of sequence nextval   lt  lt other values gt  gt       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     lt  lt other columns gt  gt

User · Answer

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

User · Answer

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

User · Answer

Starting with Oracle 12c there is support for Identity columns in one of two ways    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        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

User · Answer

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

User · Answer

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

User · Answer

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

User · 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

User · Answer

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

User · Answer

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  lt table name gt    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  lt table name gt   name  VALUES   name value      So  it will insert unique value to your emp id Column

User · Answer

Here is complete solution w r t exception error handling for auto increment  this solution is backward compatible and will work on 11g  amp  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

User · Answer

oracle has sequences AND identity columns in 12c  http   www oracle-base com articles 12c identity-columns-in-oracle-12cr1 php identity-columns  I found this but not sure what rdb 7 is http   www oracle com technetwork products rdb 0307-identity-columns-128126 pdf

User · Answer

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

User · Answer

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

User · Answer

Here are three flavors    numeric   Simple increasing numeric value  e g  1 2 3      GUID  globally univeral identifier  as a RAW datatype  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

[sql] How to create id with AUTO_INCREMENT on Oracle?

Examples related to sql

Examples related to oracle

Examples related to auto-increment