Create a table in oracle and add a primary key to set auto-growth of the primary key

Oracle sequence explanation and creation of auto-incrementing primary key Oracle sequence primary key sequence: a database object provided by oacle to generate a series of unique numbers. L automatic provision of unique value l shared object l mainly used to provide the primary key value l loading the sequence value into memory can improve access efficiency 1. first create a sequence, the syntax format of the Oracle sequence is: CREATESEQ

Oracle sequence explanation and creation of auto-incrementing primary key Oracle sequence primary key sequence: a database object provided by oacle to generate a series of unique numbers. L automatic provision of unique value l shared object l mainly used to provide the primary key value l loading the sequence value into memory can improve access efficiency 1. first CREATE a sequence, the syntax format of the Oracle sequence is: CREATE SEQ

Explain the oracle sequence and create an auto-incrementing primary key

Oracle sequence primary key
Sequence: a database object provided by oacle to generate a series of unique numbers.
L automatically provides unique values
L shared object
L mainly used to provide the primary key value
L loading sequence values into memory improves access efficiency

1. First create a sequence. The syntax format of the Oracle sequence is:

Create sequence name [increment by n] [start with n] [{MAXVALUE/MINVALUEn | NOMAXVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];

1) increment by is used to define the sequence step. If omitted, the default value is 1,
If a negative value exists, it indicates that the Oracle sequence value is decreased according to this step.
2) start with defines the initial value of the sequence (that is, the first value generated). The default value is 1.
3) MAXVALUE defines the maximum value that can be generated by the sequence generator. The NOMAXVALUE option is the default option, indicating that no maximum value is defined,
At this time, for the ascending Oracle sequence, the system can generate the maximum value of 10 to the 27 power; For the descending sequence, the maximum value is-1.
4) MINVALUE defines the minimum value generated by the sequence generator. Option NOMAXVALUE is the default option,
It indicates that no minimum value is defined. What is the minimum value that the system can produce for a descending sequence? 10 to the power of 26; for incremental sequence, the minimum value is 1.
5) CYCLE and NOCYCLE indicate whether to CYCLE when the value of the sequence generator reaches the limit value. CYCLE indicates loop, and NOCYCLE indicates no loop. If there is a loop, when the ascending sequence reaches the maximum value, it loops to the minimum value. When the descending sequence reaches the minimum value, it loops to the maximum value. If there is no loop, an error occurs when a new value is generated after the limit value is reached.
6) CACHE (buffer) defines the size of the memory block for storing the sequence. The default value is 20. NOCACHE indicates no memory buffer for the sequence.
Buffer the sequence memory to improve the sequence performance.

2. the syntax for deleting an Oracle SEQUENCE is the drop sequence name;

Use sequence will produce cracks
L The sequence has cracks in the following cases:
? Rollback
? System exception

Instance application:

1. Sqlplus is logged on first:
SQL> conn scott/tiger;
2. Table creation:
Drop table users;

   create table users(       id number(6) ,      name varchar2(30),      constraint pk_id primary key(id)   );

3. Sequence creation:
Create sequence aq1
Start with 1
Increment by 1
Minvalue 1
Max value 9999999
Nocycle
Nocache
Noorder;
/Or
Drop sequence sq1;
Create sequence sq1;
4. Trigger creation:

Create or replace trigger pn_trigger
Before insert on users
For each row
Begin
Select sq1.nextval into: new. id from sys. dual;
End;

5. Okay, insert a record to test ....

Insert into users (name) values ('zhsan ');
Select * from users;

Create a trigger

Create or replace trigger name before insert on table name for each rowbegin select sequence name. nextval into: new. Primary Key from dual; end trigger name;