Oracle PRIMARY KEY Auto-increment (1) sequence + trigger for Oracle PRIMARY KEY auto-increment

Sequence + trigger for Oracle PRIMARY KEY auto-increment

The syntax format for a sequence is:

CREATE SEQUENCE sequence Name [INCREMENT by n] [START with n] [{maxvalue/minvalue n| Nomaxvalue}][{cycle| Nocycle}][{CACHE n| NOCACHE}];

INCREMENT by

Used to define the step of the sequence, if omitted, the default is 1, and if a negative value is present, the values of the sequence are decremented by this step.

START with

Defines the initial value of the Oracle sequence (that is, the first value produced), which defaults to 1.

MAXVALUE

Defines the maximum value that the sequence generator can produce. Option Nomaxvalue is the default option, which means that there is no maximum definition, at which point the system can produce a maximum value of 10 for the increment sequence, and the maximum value for the descending sequence is-1.

MINVALUE

Defines the minimum value that the sequence generator can produce. Option Nomaxvalue is the default option, which means that there is no minimum value definition,

CYCLE and Nocycle

Indicates whether to loop after the value of the sequence generator reaches the limit value. The cycle represents the loop, and the nocycle represents no loops. If the loop is, it loops to the minimum when the increment sequence reaches its maximum value, and to the maximum value when the descending sequence reaches the minimum value. If you do not loop, the error occurs when the limit value is reached and the new value continues to be generated.

CACHE

(buffered) Defines the size of the memory block that holds the sequence, which defaults to 20. NoCache indicates that the sequence is not buffered in memory. Memory buffering of a sequence can improve the performance of the sequence.

Example:

Create a test table

---Create a test tableCREATE TABLEdepartments (ID Number(Ten) not NULL, DESCRIPTIONVARCHAR2( -) not NULL);--Add primary KeyALTER TABLEDepartmentsADD(CONSTRAINTDept_pkPRIMARY KEY(ID));

Create a sequence

-- Create a sequence CREATE SEQUENCE Dept_seq;

Equivalent to

CREATE 1 9999999999999999999999999999  by 1  with 1  Noorder nocycle nopartition

Trigger version 1: the Nextval value of the sequence is used only when it is not empty

CREATE OR REPLACE TRIGGER

INSERT  on

 for Each ROW  when  is NULL )BEGINintodual ; END ;

Trigger version 2: ignores the specified ID when inserting data

CREATE OR REPLACE TRIGGER     INSERT on for each ROW BEGIN SELECT  into from  dual;  END;

Test data

INSERT  into VALUES ' Specify ID ' ); INSERT  into VALUES (' do not specify ID');

Oracle PRIMARY KEY Auto-increment (1) sequence + trigger for Oracle PRIMARY KEY auto-increment