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