Oracle autoincrement columns

Mysql has this nice column autoincrement attribute that will create unique identifiers for that column. In oracle, it is a bit more complex to create such a fetaure, but allows any customization through the use of two specific oracle elements : sequence and trigger.

  • the first step is to create you table, including the field you plan to use as a unique autoincremented identifier :

    create table my_test (
    id number,
    my_test data varchar2(255)
  • secondly you can create a sequence for that table - a sequence is a specific oracle command that handles increments. -
    create sequence test_seq
    start with 1
    increment by 1
  • an finally you can instanciate a trigger for that table forcing auto allocation of the designated column before the insert is performed :
    create trigger test_trigger
    before insert on my_test
    for each row
    select test_seq.nextval into from dual;

    an alternative to using a trigger would be to invoke the sequence next value within the insert statement :

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



Popular Posts