Auto Incremental row in Oracle Database

Most database support auto incremental row however in Oracle database it doesn’t support that directly. But we can use sequence and trigger to implement this feature.

Why in Oracle Database it doesn’t support auto increment? Below is an answer from http://stackoverflow.com/questions/1204372/why-oracle-does-not-have-autoincrement-feature-for-primary-keys and I think it makes sense.

It may just be terminology. ‘AUTOINCREMENT’ implies that that record ‘103’ will get created between records ‘102’ and ‘104’. In clustered environments, that isn’t necessarily the case for sequences. One node may insert ‘100’,’101′,’102′ while the other node is inserting ‘110’,’111′,’112′, so the records are ‘out of order’. [Of course, the term ‘sequence’ has the same implication.]

If you choose not to follow the sequence model, then you introduce locking and serialization issues. Do you force an insert to wait for the commit/rollback of another insert before determining what the next value is, or do you accept that, if a transaction rolls back, you get gaps in the keys.

Then there’s the issue about what you do if someone wants to insert a row into the table with a specific value for that field (ie is it allowed, or does it work like a DEFAULT) or if someone tries to update it. If someone inserts ‘101’, does the autoincrement ‘jump’ to ‘102’ or do you risk attempted duplicate values.

It can have implications for their IMP utilities and direct path writes and backwards compatibility.

I’m not saying it couldn’t be done. But I suspect in the end someone has looked at it and decided that they can spend the development time better elsewhere.

 Actually in a word, ASA we have sequence, why we still need an auto-increment feature? 🙂

From MySQL:

CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM;

From Oracle Database:

CREATE SEQUENCE animals_seq
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE;

create or replace trigger animals_trg before insert on animals for each row begin
select animals_seq.nextval
into :new.animal_id
from dual;
end;
/

Another interesting thing here is the ‘dual’ table if you are new in Oracle Database. It is a special default one-column table and is suitable for use in selecting a pseudocolumn such as SYSDATE or USER.

http://en.wikipedia.org/wiki/DUAL_table