Create an auto-grow field in Oracle

Reference http://www.cnblogs.com/jerrmy/archive/2013/03/13/2958352.html

Oracle is a little different from other databases when creating a table, such as SQL Server can add identity to a field of type int (1), the field will be increased by +1, and this field will be set as the primary key, enabling us to insert the data. You can use "auto_increment" in MySQL. But Oracle is a bit cumbersome and needs to use sequences and triggers to achieve its purpose.

First we create an employee table. CREATE TABLE Employee (

Id int,
DeptNo number,
EmpNo number,
ename VARCHAR2 (16),
Job VARCHAR2 (32),
Sal float,
HireDate Date,
Constraint Pk_employee primary KEY (EmpNo)
);

Second, create the Employee table auto-grow sequence Create sequence Employ_autoinc

MinValue 1
MaxValue 9999999999999999999999999999
Start with 1
Increment by 1
NoCache

Third, create a trigger to assign the values in the sequence to the rows inserted into the employee table create or replace trigger Identity1
Before insert on test--table name
For each row
Begin
Select Employ_autoinc.nextval into:new.nId from dual;
End Finally, test our results. INSERT into employee (deptno,empno,ename,job,sal,hiredate) VALUES (520,52010      Zhou, ' James ', ' PD ', 6000,to_date (' 2012-10-22 ', ' yyyy-mm-dd ')); SELECT * from employee;

Create an auto-grow field in Oracle