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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.