hocuspokus

Auto-Increment ID's In Oracle

October 04, 2007

This is old news to most people who have been using Oracle for a while, but to me this is something new that I learnt today. How to have MySQL like “auto-increment” id’s for your tables in Oracle. :) Here’s an example…

First we need to create a sequence for the ID’s:

CREATE SEQUENCE “S_COMMENT_ID”
START WITH 1
INCREMENT BY 1
CACHE 10;

Then we create the table and the required trigger:

CREATE TABLE “COMMENT” (
  “COMMENT_ID” NUMBER(10,0) NOT NULL,
  “COMMENT_BODY” VARCHAR2(1000),
  “USER” VARCHAR2(100),
  “CREATED_DATE” DATE,
  CONSTRAINT “PK_COMMENT” PRIMARY KEY (“COMMENT_ID”)
);

CREATE OR REPLACE TRIGGER “TR_COMMENT_ID” BEFORE INSERT ON COMMENT REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN if(:new.comment_id is null) then SELECT S_COMMENT_ID.nextval INTO :new.COMMENT_ID FROM dual; end if; END; / ALTER TRIGGER “TR_COMMENT_ID” ENABLE;

And there you go - an auto-incrementing primary key… Damn that’s a lot more code to write than a simple ‘auto-increment’.

Edit: An alternative when using SQL Developer is to create your table, select it, then go to the ‘Triggers’ tab. In there you will find a useful ‘Actions…’ button where you can create a new trigger linking your tables primary key to a sequence.

Comments