Orcle create auto-increment Column

Orcle create auto-increment Column

I just used orcle. The team lead asked me to design a table. After the result is designed, I will design the primary key ID in PLSQL, because if I don't need to add an auto-increment column, I will use a composite primary key, so I want to use the auto-increment column of ID to represent it. However, he is a little different from SQL Server, and he does not add an auto-increment column. So I checked it online in two ways.

Method 1: trigger

First, create a test table t_demo.

create table t_demo(     id  number(20) primary key,     username varchar2(20))

Step 1: Create squence

Create sequence demo_seq increment by 1 -- increase to 1 start with 1 -- start from 1 minvalue 1 maxvalue 9999999999999 -- maximum nocache -- no cache order is required; sort

Step 2: Create a before insert Trigger Based on the table.

create or replace trigger userlogin_trigger before insert on usertestfor each row begin       select   test_seq.nextval   into:new.id from sys.dual ; end;

Step 3: Test

Test and insert a record to see If auto-increment exists.

Method 2

Use SQL statements and dequence.

Step 1: Create sequence like above Step 2: SQL statement

Insert into t_demo (id, username) values (test_seq.nextval, menghaibin)

Summary

I personally think it is better to use the second method. If the notes need to be maintained in the future, it is more convenient to use the second method. If a trigger is used, database changes will inevitably be applied by the trigger.