GrassField

This post is regarding auto increment in oracle. Usually I used to add AUTO_INCREMENT flag in mysql. But it is not a single word task oracle. we need to create sequence and trigger to do the same. please see the following examples 1. Here is my table structure.

CREATE TABLE FEED_DETAIL
(
FEED_ID NUMBER(6, 0),
FEED_TEXT VARCHAR2(128 BYTE),
FEED_TITLE VARCHAR2(128 BYTE),
FEED_TYPE VARCHAR2(12 BYTE),
XML_URL VARCHAR2(128 BYTE),
HTML_URL VARCHAR2(128 BYTE)
)
2. lets play with FEED_ID now. lets make it auto increment. create a sequence now

create sequence FEED_DETAIL_SEQUENCE
start with 1
increment by 1
nomaxvalue;

A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

3. Now create a trigger. A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Triggers can restrict access to specific data, perform logging, or audit data modifications.

create or replace trigger FEED_DETAIL_TRIGGER
before insert on FEED_DETAIL
for each row
begin
select FEED_DETAIL_SEQUENCE.nextval into :new.feed_id from dual;
end;

Now insert the values

insert into feed_detail (feed_text, feed_title, feed_type, xml_url, html_url) values('a', 'b', 'c', 'd', 'e');
insert into feed_detail (feed_text, feed_title, feed_type, xml_url, html_url) values('a', 'b', 'c', 'd', 'e');

Enjoy auto incrementing column now!