Monday, August 18, 2008

Identity (Autoincrement) Column on Oracle

You can do this by using a sequence and before insert trigger on oracle:

create table ABC (id int, data varchar2(200));

create sequence INCABC
start with 1
increment by 1
maxvalue 999999999999
minvalue 1
nocyclecache 10

create or replace trigger ABC_IDENTITY
before insert on ABC
select INCABC.nextval into from dual;

However in order to insert table, all column names except "id" should be provided in insert statement like:

insert into ABC (data) values ('abc');

or a dummy value should be provided for "id" column:

insert into abc values('abc',5);

other case following error occurs:

SQL> insert into abc values('abc');
insert into abc values('abc')
ERROR at line 1:
ORA-00947: not enough values

No comments: