Create auto-increment columns in Oracle

Create a sequence in the Oracle database. When using SQL statements to write data to the database, use the unique value generated by the sequence to implement auto-increment of the primary key value in the table.

Create a sequence in the Oracle database. When using SQL statements to write data to the database, use the unique value generated by the sequence to implement auto-increment of the primary key value in the table.

Sequence is a database object that can be used by multiple users to generate a series of unique numbers. Sequence definition is stored in the data dictionary. You can use sequence to automatically generate the key value of a primary key by providing a sequence table with unique values. When a sequence is called for the first time, it returns a predetermined value. In each subsequent query, the sequence will generate a value that increases according to the specified increment. The sequence can be cyclically or continuously increased until the specified maximum value is reached. The sequence creation syntax is as follows: create sequence [mode] sequence name [start with start number] [increment by increment] [maxvalue maximum value | nomaxvalue] [minvalue minimum value | nominva lue] [cycle | nocuyle] [cache quantity | nocache] [order | noorder].
The starting number, maximum value, minimum value, and increment value of the sequence can be used to determine whether the sequence is ascending or descending, and the number of each increment or subtraction. The Nocyle option is used to determine that no more values can be generated after the sequence reaches the maximum (ascending sequence) or minimum (descending sequence) to prevent sequence rotation.
Create a sequence in the Oracle database. When using SQL statements to write data to the database, use the unique value generated by the sequence to implement auto-increment of the primary key value in the table. For example:

SQL> createtable tablename

(Id number notnull ,...);


SQL> createsequence autoID increment by 1 start with 1 maxvalue 999999 cycle;


SQL> insertinto tablename values (autoID. nextval ,...);
Multiple users can share a sequence, but it is for all tables, so the generated sequence number is unique but not continuous for a table.
Use a trigger to generate a primary key value.
In a data table, the primary key value needs to be automatically increased, but in an Oracle database, there is no data type that automatically increases like the Autoincrement of Mysql. DML triggers are used to implement auto-increment functions of Oracle database fields.
Trigger is a process that is executed when a specific database event occurs. You can use the trigger to expand the integrity of the reference. DML is a data manipulation language used by users or programmers to operate data in databases. There are two basic data operations: Search (query) and update (insert, delete, and modify ). A trigger is similar to a function and a process and exists as an independent entity in the database. The trigger event can be a DML (insert, update, or delete) operation on the database table. DML triggers are currently the most widely used triggers, that is, the triggers triggered by DML statements. This statement determines the type of DML triggers. The trigger events include insert, update, and delete ). Any trigger event can create before triggers and after triggers for each trigger event. For example, you can create a before insert Statement on the table to take action before the insert event occurs.
The syntax for creating a trigger is as follows:

Create [orreplace] trigger name

{Before | after | insteadof} triggers a trigger event.

Referencing_clause

[WHEN trigger_condition]

[For each row]
Referencing_clause is used to reference data in rows in the modifying state. If trigger_condition is specified in the WHEN clause, the condition is evaluated first. The trigger subject runs only when the condition is true. With the combination of triggers and sequences, You can automatically increase the primary key value in the table during DML operations. The implementation steps are as follows.

Droptable book;
-- Create a table
Createtable book (
BookId varchar2 (4) primarykey,
Name varchar2 (20)
);
-- Create Sequence
Createsequence book_seq start with 1 increment by 1;

-- Create a trigger
Createorreplacetrigger book_trigger
Before inserton book
For each row
Begin
Select book_seq.nextval into: new. bookId from dual;
End;
-- Add data
Insertinto book (name) values ('cc ');
Insertinto book (name) values ('dd ');

Commit;

Demo:

Create table t_mid_RoleToOrg (FID VARCHAR2 (20) primary key, FRolemappingid varchar (150), FOrgNumber varchar (150 ));
-- Create a sequence
Create sequence seq_test start with 1 increment by 1 CACHE 20;
Commit;
-- Create a trigger
Create trigger seq_trigger
Before insert on t_mid_RoleToOrg for each row when (new. fid is null)
Begin
Select seq_test.nextval into: new. fid from dual;
End;

Insert into t_mid_RoleToOrg (FRolemappingid, Forgnumber) values ('Liu yun', 'Liu yun ');

SELECT * FROM t_mid_RoleToOrg;

Drop trigger seq_trigger;
Drop sequence seq_test;
Drop table t_mid_RoleToOrg;