Application of Oracle Auto-increment Fields

Today we will introduce Oracle Auto-increment fields. We all know that Oracle and ms SQL are different. By default, auto-increment columns do not exist, however, we can create a sequence and trigger to create an auto-incrementing column. The following describes how to create an auto-incrementing column in Oracle 11g ).

1. Create a TestTable table,

 
 

  
  
CREATE TABLE TestTable  
  
  
(  
  
  
ID NUMBER NOT NULL,  
 
 

Used for Oracle Auto-incrementing Column

 
 

  
  
NAME VARCHAR2(40)  
  
  
)  
  
  
;  
 
 

2. Create a Sequence) and name it SEQ. We recommend that you use the built-in visualization tool to create 'dunk', which is the user name I log on to. Do not copy it all ).

 
 

  
  
CREATE SEQUENCE "DUNCAN"."SEQ" MINVALUE 1 
MAXVALUE 999999999999999999999999999  
  
  
INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE ;  
 
 

3. Create a trigger

 
 

  
  
create or replace  
  
  
TRIGGER Test_Trr  
  
  
BEFORE INSERT ON TestTable   
 
 

Create a trigger on 'testtable'

 
 

  
  
FOR EACH ROW  
  
  
BEGIN  
  
  
SELECT SEQ.NEXTVAL INTO :new.ID FROM sys.DUAL;  
 
 

'Nextval = increase the sequence value, then return the sequence Value and insert this value into the ID'

 
 

  
  
END; 
 
 

Now, the Oracle Auto-increment column in The TestTable table is created. Just insert a piece of data to your colleagues!