hocuspokus

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:

1
2
3
4

CREATE SEQUENCE "S_COMMENT_ID"
START WITH 1
INCREMENT BY 1
CACHE 10;

Then we create the table and the required trigger:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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.