Autoincrements in Oracle PHP & inserted ID

If you're starting out working with Oracle and PHP (my sympathies) then it's possible you're having issues trying to understand the best ways to setup autoincrements with your database tables as well as retrieving the inserted ID upon running insertion queries as I did. It is not as easy and straight forward as is with MySQL or Postgres.

So what I'll do is outline the basics of what you'll need to know to get started.

There are two approaches that I know of depending on your use cases. The first approach is to use a "IDENTITY" column. Here is a sample table:

CREATE TABLE "TEST_TABLE"
  (
     "ID"                 NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
     "TEXT" VARCHAR2(255 byte),
     CONSTRAINT "TEST_TABLE_ID_NOT_NULL" CHECK ("ID" IS NOT NULL) ENABLE,
     CONSTRAINT "TEST_TABLE_PK" PRIMARY KEY ("ID") ENABLE
  );

And from here you can run queries of the form:

INSERT INTO SCHEMA.TEST_TABLE (TEXT) VALUES ('test') RETURNING ID INTO :id

Where :id is a prepared value.

However this approach isn't perfect. If you're doing any replication this way may not be compatible. In which case you will have to use sequences.

What you would do is create a sequence such as:

CREATE SEQUENCE TEST_SEQ START WITH 1;

And drop the identity from the ID column:

CREATE TABLE "TEST_TABLE"
  (
     "ID"                 NUMBER,
     "TEXT" VARCHAR2(255 byte),
     CONSTRAINT "TEST_TABLE_ID_NOT_NULL" CHECK ("ID" IS NOT NULL) ENABLE,
     CONSTRAINT "TEST_TABLE_PK" PRIMARY KEY ("ID") ENABLE
  );

Upon insertion you would want to do something like this:

INSERT INTO SCHEMA.TEST_TABLE (ID, TEXT) VALUES (SCHEMA.TEST_SEQ.NEXTVAL, 'test') RETURNING ID INTO :id

Hope that helps