How to create an identity column in Oracle 11 g?

Hi all,

I was just wondering about the fact that how to create an auto increment column in oracle. It is quite easy to create an auto increment column in sql server but in oracle’s old version that is 11, it is bit of an overwork for you. Well it is not that difficult if you are using SQL developer but in case you are creating the table from CLI on Unix or other CLI based operating system than you need to know that to create an auto increment column in oracle you need to first declare a sequence using the below structured query:

create sequence name_of_sequence

Once you have executed the above query you will have to create a trigger of type before on the newly created table where you want the auto incremented column. The structured query for the same is mentioned below:

create or replace trigger name_of_trigger before insert on new_tbl_name
for each row
select name_of_seq.nextval into :new.auto_incremented_col_name from dual

Congrats you have just created the column with auto incremented values

This entry was posted in Oracle. Bookmark the permalink.

Comments are closed.