How to Create Id With Auto_Increment on Oracle

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

How to create id with AUTO_INCREMENT on a view in Oracle

While it's not possible to return a single unique identity column for a view whose underlying data does not have any single unique identifier, it is possible to return composite values that uniquely identify the data. For example given a table of CSV Data with a unique ID on each row:

create table sample (id number primary key, csv varchar2(4000));

where the CSV column contains a string of comma separated values:

insert into sample 
select 1, 'a' from dual union all
select 2, 'b,c' from dual union all
select 3, 'd,"e",f' from dual union all
select 4, ',h,' from dual union all
select 5, 'j,"",l' from dual union all
select 6, 'm,,o' from dual;

The following query will unpivot the csv data and the composite values (ID, SEQ) will uniquely identify each VALue, The ID column idetifies the record the data came from, and SEQ uniquely identifies the position in the CSV:

WITH pvt(id, seq, csv, val, nxt) as (
SELECT id  -- Parse out individual list items
     , 1   -- separated by commas and 
     , csv -- optionally enclosed by quotes
     , REGEXP_SUBSTR(csv,'(["]?)([^,]*)\1',1,1,null,2)
     , REGEXP_INSTR(csv, ',', 1, 1)
FROM sample
UNION ALL
SELECT id
     , seq+1
     , csv
     , REGEXP_SUBSTR(csv,'(["]?)([^,]*)\1',nxt+1,1,null,2)
     , REGEXP_INSTR(csv, ',', nxt+1, 1)
FROM pvt
 where nxt > 0
)
select * from pvt order by id, seq;

        ID        SEQ CSV        VAL               NXT
---------- ---------- ---------- ---------- ----------
         1          1 a          a                   0

         2          1 b,c        b                   2
         2          2 b,c        c                   0

         3          1 d,"e",f    d                   2
         3          2 d,"e",f    e                   6
         3          3 d,"e",f    f                   0

         4          1 ,h,        [NULL]              1
         4          2 ,h,        h                   3
         4          3 ,h,        [NULL]              0

         5          1 j,"",l     j                   2
         5          2 j,"",l     [NULL]              5
         5          3 j,"",l     l                   0

         6          1 m,,o       m                   2
         6          2 m,,o       [NULL]              3
         6          3 m,,o       o                   0


15 rows selected.

How to create id with AUTO_INCREMENT by 5 and start from 2000

You can use IDENTITY column as follows:

CREATE TABLE example (
     id GENERATED ALWAYS AS IDENTITY (START WITH 2000 INCREMENT BY 5),
     name VARCHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

Auto Increment for Oracle

Create the table and the sequence

SQL> create table staff (
  2    emp_id number primary key,
  3    staff_name varchar2(100)
  4  );

Table created.

SQL> create sequence emp_id_seq;

Sequence created.

Now, you can create a trigger that uses the sequence to populate the primary key

SQL> create trigger trg_emp_id
  2    before insert on staff
  3    for each row
  4  begin
  5    select emp_id_seq.nextval
  6      into :new.emp_id
  7      from dual;
  8  end;
  9  /

Trigger created.

Now, when you insert data, you woon't need to specify the EMP_ID column-- it will automatically be populated by the trigger

SQL> insert into staff( staff_name ) values ('Justin');

1 row created.

SQL> select * from staff;

    EMP_ID STAFF_NAME
---------- --------------------
         1 Justin

How to create TRIGGER column id auto increment with string in oracle database?

Should be

:NEW.id := :NEW.id || 'S-' || to_char(seq_log.nextval);

---

Related Topics