SEQUENCE Oracle to generate unique IDs

Oracle does not have auto_increment

Unlike MySQL which has AUTO_INCREMENT, Oracle uses a separate object named SEQUENCE to generate ever-increasing numbers.

Creating a sequence

CREATE SEQUENCE artikel_seq
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

Wearing sequence at insert

INSERT INTO artikel (id, judul)
VALUES (artikel_seq.NEXTVAL, 'Mengenal Oracle Sequence');

-- Mengambil nilai terakhir yang dipakai pada sesi ini
SELECT artikel_seq.CURRVAL FROM dual;

NEXTVAL generates a new number every time it is called, whereas CURRVAL reading the last value already taken in the same session.

Automate via trigger or identity column

Oracle 12c and above support GENERATED AS IDENTITY which combines sequence and column autofill in one definition.

CREATE TABLE artikel (
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    judul VARCHAR2(200) NOT NULL,
    CONSTRAINT artikel_pk PRIMARY KEY (id)
);

Pay attention to the number gap

Sequence can leave a number gap if a transaction that takes a value is canceled, because NEXTVAL do not follow the rollback. Do not rely on the sequence as a sequential row counter without gaps.

practice;

Create a new table with the identity column, then compare the results with the manual sequence approach in the table artikel over