DB Wiki

Force use of identity, generate error if statement references identity column

CREATE TABLE test_table (
  id          NUMBER GENERATED ALWAYS AS IDENTITY,
  description VARCHAR2(40)
);

If statement references identity column then use that value.
Use the identity value if the column isn't referenced.
Cannot use null value.

CREATE TABLE test_table (
  id          NUMBER GENERATED BY DEFAULT AS IDENTITY,
  description VARCHAR2(40)
);

If statement references identity column then use that value.
Use the identity value if the column isn't referenced.
Can use a null value.

CREATE TABLE test_table (
  id          NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  description VARCHAR2(40)
);

Start the identity number with 100 and increment each value by 10

CREATE TABLE test_table (
  id          NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 100 INCREMENT BY 10),
  description VARCHAR2(30)
);