Oracle Auto-increment column Creation Method

Recently, I was working on an oracle project. Since I have never been familiar with Oracle development before, I encountered many problems. For example, adding an auto-incrementing column to an oracle table is different from that of SQL Server.

 

Oracle does not have the auto-increment field function, but it can be implemented through trigger and sequence.

Create a test table first:

Create Table userlogin
(

ID number (6) Not null,

Name varchar2 (30) not null primary key

)

Tablespace users

/

 

Step 1: Create a sequence

Create sequence userlogin_seq increment by 1 start with 1 minvalue 1 maxvalue 9999999999999 nocache order;

Step 2: Create a before insert Trigger Based on the table and use the created sequence in the trigger

Create or replace trigger userlogin_trigger
Before insert on userlogin
For each row
Begin
Select userlogin_seq.nextval into: New. ID from SYS. Dual;
End;

/

 

Step 3: Test in the userlogin table

Write an insert statement and insert a record. If the ID field is not auto-incrementing, the auto-incrementing statement is OK.