How to implement primary key auto-increment in Oracle

Method: 1. Use "create sequence sequence name minvalue..." to create a sequence; 2. Use "create or replace trigger trigger name before insert on message. table name..." to create a trigger. Can.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How does oracle realize primary key auto-increment

Oracle cannot directly select auto-increment like mysql when creating a table, but in oracle we can add sequences and triggers to the table structure. To realize the primary key auto-increment.

This is a PolarDB database, but its usage is still very similar to Oracle.

This is a newly created table structure, with id as the primary key.

Step one: Create a sequence

create sequence sms_id
minvalue 1
nomaxvalue 
increment by 1 
start with 1
nocache;

Other ways to use the sequence

Query sequence

select sms_id.currval from dual //查询当前id是第几

Delete sequence

DROP SEQUENCE sms_id;

Step 2: Create trigger

create or replace trigger sms_tg_insertId
before insert on message.oa_doc_smsinfo 
for each row 
begin
select sms_id.Nextval into:new.id from dual;
end;

Other ways to use triggers

Delete triggers

drop trigger sms_tg_insertId

Step 3: Add data test

After running three times, the id automatically grows.

Oracle Video Tutorial"

The above is the detailed content of How to implement primary key auto-increment in Oracle. For more information, please follow other related articles on the PHP Chinese website!