The Curious Case of AUTO REST for a TABLE with an IDENTITY Column

I spent some time helping an internal web developer yesterday with the AUTO REST feature for TABLES. He needed to be able to POST up new records to a table.

This TABLE was just fine, however the way it’s PRIMARY KEY was defined was causing some problems with the AUTO feature.

When you define a COLUMN using this ‘BY DEFAULT’ property, the database will ALWAYS generate a value for a new row.

The way AUTO REST works for POST on a TABLE, you need to submit a value for EVERY column.

This create a problem when…

It doesn’t like that we’re providing a value for the ID column – that’s the IDENTITY column’s job!

It also creates a problem when…

Since I’m running my own ORDS, I can set it to DEBUG mode AND enable PRINT ERRORS TO SCREEN. You obviously want to be careful about this in a PROD environment. And since I know how AUTO works and what those error messages mean, I basically already know HOW to fix it. But let’s keep going.

So how do we ‘fix’ this?

Option One: Build your own POST.

I’m not going to share the code for this – it’s not suitable for PROD, but you get the idea.

Option Two: ALTER your TABLE

This works when the column is set to Generate On NULL, vs ALWAYS

Here’s the original TABLE DDL, followed by the ALTER code I ran to ‘fix’ the table so it would work with the AUTO REST POST.

CREATE TABLE IDENTITY_TABLE 
(
  ID INTEGER GENERATED ALWAYS AS IDENTITY INCREMENT BY 1 START WITH 1 MINVALUE 1 CACHE 100 NOT NULL 
, WORDS VARCHAR2(256) 
, CONSTRAINT IDENTITY_TABLE_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
);
 
 
ALTER TABLE IDENTITY_TABLE MODIFY
(
  ID GENERATED BY DEFAULT ON NULL AS IDENTITY 
);