Creating Auto‑Increment Columns in OceanBase Oracle Tenant (OBOracle) – Four Methods

OceanBase provides both MySQL and Oracle tenants; the Oracle tenant (referred to as OBOracle) does not support the MySQL AUTO_INCREMENT attribute, so auto‑increment functionality must be achieved through Oracle‑style sequences.

Four Methods to Create Auto‑Increment Columns in OBOracle

Method 1: SEQUENCE + DML

Create a sequence and use sequence_name.nextval explicitly in each INSERT statement.

CREATE SEQUENCE sequence_name
    MINVALUE 1
    MAXVALUE 999999
    START WITH 100
    INCREMENT BY 1;

INSERT INTO test(ID, NAME, AGE) VALUES (sequence_name.nextval, 'A', 18);
INSERT INTO test(ID, NAME, AGE) VALUES (sequence_name.nextval, 'B', 19);
INSERT INTO test(ID, NAME, AGE) VALUES (sequence_name.nextval, 'C', 20);

Method 2: SEQUENCE + DDL

Create a sequence and set the column’s DEFAULT to sequence_name.nextval so that inserts need not specify the ID.

CREATE TABLE Atable (
    ID NUMBER NOT NULL PRIMARY KEY,
    NAME VARCHAR2(480),
    AGE NUMBER(10,0)
);

CREATE SEQUENCE A_seq START WITH 10 INCREMENT BY 1;
ALTER TABLE Atable MODIFY ID DEFAULT A_seq.nextval;

INSERT INTO Atable(NAME, AGE) VALUES ('zhangsan', 18);
INSERT INTO Atable(NAME, AGE) VALUES ('lisi', 19);

Method 3: SEQUENCE + Trigger

Create a sequence and a BEFORE INSERT trigger that assigns sequence_name.nextval to the ID column automatically.

CREATE SEQUENCE B_seq START WITH 1 INCREMENT BY 1;
CREATE TABLE Btable (
    ID NUMBER,
    NAME VARCHAR2(480),
    AGE NUMBER(10,0)
);
CREATE OR REPLACE TRIGGER set_id_on_Btable
BEFORE INSERT ON Btable
FOR EACH ROW
BEGIN
    SELECT B_seq.NEXTVAL INTO :new.id FROM dual;
END;
/

INSERT INTO Btable(NAME, AGE) VALUES ('zhangsan', 18);
INSERT INTO Btable(NAME, AGE) VALUES ('lisi', 19);

Method 4: GENERATED BY DEFAULT AS IDENTITY

Use the Oracle 12c+ identity syntax when creating the table; the database automatically creates an internal sequence and populates the column.

CREATE TABLE Ctable (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY
        MINVALUE 1 MAXVALUE 999999 INCREMENT BY 1 START WITH 1 PRIMARY KEY,
    NAME VARCHAR2(480),
    AGE NUMBER(10,0)
);

INSERT INTO Ctable(NAME, AGE) VALUES ('zhangsan', 18);
INSERT INTO Ctable(NAME, AGE) VALUES ('lisi', 19);

Summary

Method 1 (SEQUENCE + DML) requires specifying the sequence in every INSERT and is not recommended.

Method 2 (SEQUENCE + DDL) simplifies INSERTs but still needs a manually created sequence per table; not recommended.

Method 3 (SEQUENCE + Trigger) adds runtime overhead and is also not recommended.

Method 4 (GENERATED BY DEFAULT AS IDENTITY) is the most convenient, performance‑friendly, and strongly recommended.