Generating unique IDs in Oracle

Unlike MySQL's AUTO_INCREMENT, Oracle has traditionally used sequences — independent database objects that generate unique numbers. Since 12c, there's also an identity column option.

Recipe #1 - Creating and using a sequence

CREATE SEQUENCE emp_seq
  START WITH 1000
  INCREMENT BY 1
  NOCACHE
  NOCYCLE;

Use it in an INSERT:

INSERT INTO emp (empno, ename, sal)
VALUES (emp_seq.NEXTVAL, 'NEW_EMP', 3000);

Get the current value (only works after calling NEXTVAL in the same session):

SELECT emp_seq.CURRVAL FROM DUAL;

Recipe #2 - Auto-populate IDs with a trigger (pre-12c)

Before Oracle 12c, the standard pattern was to use a trigger:

CREATE OR REPLACE TRIGGER emp_bi_trg
  BEFORE INSERT ON emp
  FOR EACH ROW
BEGIN
  IF :NEW.empno IS NULL THEN
    :NEW.empno := emp_seq.NEXTVAL;
  END IF;
END;

Now you can insert without specifying the ID:

INSERT INTO emp (ename, sal) VALUES ('SMITH', 2000);

Recipe #3 - Identity columns (Oracle 12c+)

Oracle 12c introduced identity columns — the modern way to auto-generate IDs:

CREATE TABLE new_employees (
  id     NUMBER GENERATED ALWAYS AS IDENTITY,
  name   VARCHAR2(100) NOT NULL,
  salary NUMBER
);

Options for identity columns:

• GENERATED ALWAYS — Oracle always generates the value; you can't specify it
• GENERATED BY DEFAULT — Oracle generates if you don't provide a value
• GENERATED BY DEFAULT ON NULL — Oracle generates if you provide NULL

CREATE TABLE orders (
  order_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY
    (START WITH 10000 INCREMENT BY 1),
  customer VARCHAR2(100),
  amount   NUMBER
);

Recipe #4 - Useful sequence queries

Check all sequences and their current state:

SELECT sequence_name, min_value, max_value,
       increment_by, last_number, cache_size
FROM user_sequences
ORDER BY sequence_name;

Reset a sequence (Oracle 18c+):

ALTER SEQUENCE emp_seq RESTART START WITH 1;

For pre-18c, you have to drop and recreate, or use the increment trick:

-- Get current value, then set negative increment
ALTER SEQUENCE emp_seq INCREMENT BY -999;
SELECT emp_seq.NEXTVAL FROM DUAL;
ALTER SEQUENCE emp_seq INCREMENT BY 1;