Oracle - Modify an existing table to auto-increment a column

Question

I have a table with the following column:

NOTEID      NUMBER NOT NULL,

For all intents and purposes, this column is the primary key. This table has a few thousand rows, each with a unique ID. Before, the application would SELECT the MAX() value from the table, add one, then use that as the next value. This is a horrible solution, and is not transaction or thread safe (in fact, before they didn't even have a UNIQUE constraint on the column and I could see the same NOTEID was duplicated in 9 different occasions)..

I'm rather new to Oracle, so I'd like to know the best syntax to ALTER this table and make this column auto-increment instead. If possible, I'd like to make the next value in the sequence be the MAX(NOTEID) + 1 in the table, or just make it 800 or something to start out. Thanks!

Solution 1

If your MAX(noteid) is 799, then try:

CREATE SEQUENCE noteseq
    START WITH 800
    INCREMENT BY 1

Then when inserting a new record, for the NOTEID column, you would do:

noteseq.nextval

Solution 2

You can't alter the table. Oracle doesn't support declarative auto-incrementing columns. You can create a sequence

CREATE SEQUENCE note_seq
  START WITH 800
  INCREMENT BY 1
  CACHE 100;

Then, you can create a trigger

CREATE OR REPLACE TRIGGER populate_note_id
  BEFORE INSERT ON note
  FOR EACH ROW
BEGIN
  :new.note_id := note_seq.nextval;
END;

or, if you want to allow callers to specify a non-default NOTE_ID

CREATE OR REPLACE TRIGGER populate_note_id
  BEFORE INSERT ON note
  FOR EACH ROW
BEGIN
  IF( :new.note_id is null )
  THEN 
    :new.note_id := note_seq.nextval;
  END IF;
END;

Comments

[Mike Christensen]

Is it possible to make the column default to the next value in the sequence if not otherwise specified?

[Justin Cave]

@Mike - You an create a trigger on the table that implements that logic, sure

[Mike Christensen]

Got it - So it's not like Postgres where you can make that the DEFAULT value, or mySQL which has the "SERIAL" column type that increments in value.

[roartechs]

Right, there is no way to specify a default incremental value like in MySQL.

[Mike Christensen]

Thanks for the detailed answer! I'm gonna accept roartechs's answer because you already have 30,000 :)

[roartechs]

Hahahaha that's funny. Is that how we accept answers now? Pity the guy with less rep?? Thanks!

[Mike Christensen]

Well also, your answer was first and technically satisfied the requirements of my question :) And I think I've accepted like 20 of Justin's answers on various Oracle questions the past few weeks heh.