How to set a primary key in Oracle and let it grow automatically

Because there is no automatic growth in Oracle, you need to do the writing trigger, and so on to set up:

Look for someone else to write, there are two ways to set the primary key, one is self-growing primary key, and the other is to generate a unique sequence.

First, self-growth primary key

I create a user's information table

--Create table
CREATE TABLE USERINFO
(
Userno Number is not NULL,
USERNAME NVARCHAR2 (20),
Userpwd NVARCHAR2 (20)
)
Tablespace myproduct
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
Initial 64K
Next 1M
Minextents 1
Maxextents Unlimited
);
--Create/recreate primary, unique and foreign KEY constraints
ALTER TABLE USERINFO
Add constraint Userno primary key (Userno)
Using index
Tablespace myproduct
Pctfree 10
Initrans 2
Maxtrans 255
Storage
(
Initial 64K
Next 1M
Minextents 1
Maxextents Unlimited
);

In fact, you can also use Plsql to map the interface settings, I do so, in order to learn, the generated SQL code is copied and posted here.

Mainly for the future study, and for other people's reference.

Here is my SQL statement to set the trigger:

Create sequence Seq_userinf start with 1 increment by 1
Nomaxvalue
Nominvalue
NoCache

CREATE OR REPLACE TRIGGER tg_test
Before INSERT on Userinfo
For each ROW when (new. Userno is null)
Begin
Select seq_userinf. Nextval Into:new.userNO from dual;
End

To insert a statement:
Insert into UserInfo (USERNAME,USERPWD) VALUES (' d ', ' d ');

My table is there is a data, the first time when inserting back out the wrong dialog box, the second time can be

Explanation of the above-input:

-Build a sequence seq_userinf
Create sequence Seq_userinf
MinValue 1--Minimum value
Nomaxvalue--Do not set the maximum value
Start with 1-counting starting from 1
Increment by 1--add 1 each time
Nocycle--keep accumulating, not looping
NoCache --Do not build buffers

After execution you can look at the structure:

Second, unique sequence

Sys_guid () generates a unique encoding of 32 bits.

The number generated by the sequence generator can only be guaranteed to be unique within a single instance, which is not intended to be used as the primary key in a parallel or remote environment because sequences in their respective environments may generate the same number, resulting in conflicts. Sys_guid will ensure that the identifiers it creates are unique within each database.
In addition, the sequence must be part of a DML statement, so it requires a round-trip to the database (otherwise it cannot guarantee that its value is unique). Sys_guid is derived from timestamps and machine identifiers that do not require access to the database, which saves query consumption.

Many applications rely on the sequence builder to create the primary key of the data row, which does not have a significant primary value, which means that the creation of a record in such a dataset will change the data column. As a result, an administrator might be interested in using SYS_GUID as the primary key instead of the sequence number in the table. This is useful in situations where objects are generated in different databases of different machines and need to be merged later.

Using Sys_guid or sequences can result in performance depletion in some areas of the database usage cycle, where the problem is. For Sys_guid, the performance impact is on query time and creation time (more blocks and indexes are created in the table to accommodate the data). For a sequence, the performance impact during the query, at this time, the buffer of the SGA sequence is exhausted. By default, a sequence buffers 20 values at a time. If the database is closed without using these values, they will be lost.

Another notable disadvantage of the values generated by Sys_guid is that managing these values can become much more difficult. You must either enter them (manually) or populate them with a script, or pass them as Web parameters. For these reasons, it is not a good idea to use Sys_guid as a primary key unless you are in a parallel environment or want to avoid using the Manage Sequence builder.

How to set a primary key in Oracle and let it grow automatically