如何让oracle里面的主键自动递增 autoincrement primary key for oracle

Suppose you have a database and you want each entry to be identifiedby a unique number. You can do this easily in mysql by specifying"auto_increment" for your number, but Oracle makes you work a littlemore to get it done.

Here is one way to do it by creating two database objects, a sequence and a trigger. I find myself wanting to do thisevery now and then but not often enough that I remember the syntaxfrom time to time, so I decided it was time to write myself up alittle cheat sheet. This is an extremely basic outline, so please try it first on a test table if you don't know what you're doing.

1. Let's say we have a table called "test" with two columns, id andtestdata. (This is just a dumb quick example, so I won't botherto specify any constraints on id.)

create table test (id number, testdata varchar2(255)); 

2. Next we'll create a sequence to use for the id numbers in our testtable.

create sequence test_seq 
start with 1 
increment by 1 
nomaxvalue; 

You could change "start with 1" to any number you want to beginwith (e.g. if you already have 213 entries in a table and you want to begin using this for your 214th entry, replace with "start with 214").The "increment by 1" clause is the default, so you could omit it.You could also replace it with "increment by n" if you want it to skip n-1 numbers between id numbers. The "nomaxvalue" tells it tokeep incrementing forever as opposed to resetting at some point. i(I'm sure Oracle has some limitation onhow big it can get, but I don't know what that limit is).

3. Now we're ready to create the trigger that will automatically insertthe next number from the sequence into the id column.

create trigger test_trigger
before insert on test
for each row
begin
select test_seq.nextval into :new.id from dual;
end;
/

Obviously you would replace "test_trigger" with something alittle more meaningful for the database table you want touse it with, "test" would be your table name, and the "id"in :new.id would be replaced with the name of the column.Every time a new row is inserted into test, the trigger will getthe next number in the sequence from test_seq and set the "id"column for that row to whatever the sequence number is. Notethat sequences sometimes appear to skip numbers because Oraclecaches them to be sure that they are always unique, so this maynot be your ideal solution if it's really important that theid is exactly sequential and not just mostly sequential and always unique.

---

Greg Malewski writes:

You've demonstrated an implementation using triggers. This is not necessary,since instead it can be included as part of the INSERT statement. Using yourexample, my INSERT statement would be:

    
insert into test values(test_seq.nextval, 'voila!');

---

Here are a couple of questions the above might raise. Thisis pretty intuitive stuff, but I'm aiming it at the Oracle newbiesince no expert would be reading this page anyway.

How do you tell what sequences and triggers are already out there?

select sequence_name from user_sequences;
select trigger_name from user_triggers;

How do you get rid of a sequence or trigger you created?

drop sequence test_seq;
drop trigger test_trigger;

Again, replace test_seq and test_trigger with the specificnames you used. You can also keep the trigger but disable itso it won't automatically populate the id column with everyinsert (and enable it again later if you want):

alter trigger test_trigger disable;
alter trigger test_trigger enable;