Two auto-increment methods for Oracle Database primary key (SEQUENCE and trigger)

Two auto-increment methods for Oracle Database primary key (SEQUENCE and trigger)
1. Preparations

Create an Oracle database table. In the user table SYS_USERS, user_id is the primary key.

-- Create tablecreate table SYS_USERS(  user_id     NUMBER(9) not null,  user_name   VARCHAR2(20) not null,  user_pwd    VARCHAR2(20) not null,  full_name   VARCHAR2(20),  sex         VARCHAR2(1))

2. Auto-increment Using Sequence

Setting the growth policy for ID is sequence, while specifying the name of sequence, it is better to create a sequence for each table, just like automatic growth in the MS-SQL, without the need to create a trigger:

1. Create SEQ

Run the following SQL statement to create a table:

--- Auto-incrementing sequence create sequence T_SYS_USER_SEQMINVALUE 1 nomaxvalue increment by 1 start with 1 NOCACHE;

T_SYS_USER_SEQ is the name of the sequence. The sequence starts from 1.

2. Inserted SQL

Insert into sys_users (user_id, user_name, user_pwd, sex) values (T_SYS_USER_SEQ.NEXTVAL, 'shaoduo', 'shao111', 'male'); commit;

The insert statement uses sequential name. NEXTVAL at the position of the id field.
T_SYS_USER_SEQ.NEXTVAL is used here.
In this way, you can use SEQ to implement auto-increment.
In actual development, fields may not be generated by specifying IDs in SQL statements.
For example
Insert into sys_users (user_name, user_pwd, sex) values ('shaoduo', 'shao111', 'male ')
This statement is generated by mapper. xml in mybatis reverse engineering. Unless the er you write can be used, it is best not to modify the reverse engineering. Therefore, to use a statement without the specified id auto-increment, select the trigger to implement auto-increment.

3. Auto-increment using triggers

The trigger method also needs to create a SEQUENCE, and then trigger the SEQUENCE setting.

1. Create SEQ

Run the following SQL statement to create a table:

--- Auto-incrementing sequence create sequence T_SYS_USER_SEQMINVALUE 1 nomaxvalue increment by 1 start with 1 NOCACHE;

2. Set the trigger

Run the following statement:

-- Create trigger create or replace trigger t_sys_user_id_trigger before insert on SYS_USERS for each rowbegin select into: new. user_id from dual; end T_SYS_USER_USER_ID_TRIGGER;

Note that you just need to change the four items to your own.
T_SYS_USER_USER_ID_TRIGGER is the name of the Set trigger.
SYS_USERS is the table name.
T_SYS_USER_SEQ is the sequence name.
New. user_id is new. Primary Key

3. Inserted SQL

Ignore id directly insert, id database automatically add

Insert into sys_users (user_name, user_pwd, sex) values ('shaoduo', 'shao111', 'male'); commit;