Why I can not insert a row with the primary key defined as NULL?

I am beginning with Oracle 12c.

I have created the following table via APEX sql workshop:

CREATE TABLE  "WS_ADDRESS" 
   (    "WS_ADDRESS_ID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
    "ADDRESS_TYPE" VARCHAR2(132), 
    "NAME" VARCHAR2(132), 
    "ADDRESS_LINE_1" VARCHAR2(132), 
    "ADDRESS_LINE_2" VARCHAR2(132), 
    "ADDRESS_LINE_3" VARCHAR2(132), 
    "TOWN" VARCHAR2(132), 
    "COUNTY" VARCHAR2(132), 
    "ZIP" VARCHAR2(132), 
    "COUNTRY" VARCHAR2(132), 
    "WS_CUSTOMER_ID" NUMBER, 
     CONSTRAINT "WS_ADDRESS_PK" PRIMARY KEY ("WS_ADDRESS_ID")
  USING INDEX  ENABLE
   )
/
ALTER TABLE  "WS_ADDRESS" ADD CONSTRAINT "WS_ADDRESS_CON" FOREIGN KEY ("WS_CUSTOMER_ID")
      REFERENCES  "WS_CUSTOMER" ("WS_CUSTOMER_ID") ENABLE
/

Where WS_ADDRESS_ID is intended to be the primary key (instead of ROWID). I have read that the primary ID shall be now be defined as:

GENERATED BY DEFAULT ON NULL AS IDENTITY

Is above definition congruent with this?

If I try to insert an element:

insert into ws_address (ws_address_id, address_type, name) values (NULL, 'FOO', 'BAR');

I get:

ORA-01400: cannot insert NULL into ("WKSP_USER"."WS_ADDRESS"."WS_ADDRESS_ID")
ORA-06512: at "SYS.DBMS_SQL", line 1721


1. insert into ws_address (ws_address_id, address_type, name) values (NULL, 'FOO', 'BAR');

I know the old approach of using a trigger, but I would like to avoid if there is an easier way in Oracle 12c to get the database assign an ID when the field is inserted with a NULL value.

Solution

It will work fine if you change it as you quoted above GENERATED BY DEFAULT ON NULL AS IDENTITY, do not forget to include ON NULL:

CREATE TABLE  "WS_ADDRESS"  
   ( 
    "WS_ADDRESS_ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE,  
    "ADDRESS_TYPE" VARCHAR2(132),  
    "NAME" VARCHAR2(132),  
    "ADDRESS_LINE_1" VARCHAR2(132),  
    "ADDRESS_LINE_2" VARCHAR2(132),  
    "ADDRESS_LINE_3" VARCHAR2(132),  
    "TOWN" VARCHAR2(132),  
    "COUNTY" VARCHAR2(132),  
    "ZIP" VARCHAR2(132),  
    "COUNTRY" VARCHAR2(132),  
    "WS_CUSTOMER_ID" NUMBER,  
     CONSTRAINT "WS_ADDRESS_PK" PRIMARY KEY ("WS_ADDRESS_ID") 
  USING INDEX  ENABLE 
   );
insert into ws_address (ws_address_id, address_type, name) values (NULL, 'FOO', 'BAR')

Example: https://livesql.oracle.com/apex/livesql/s/l4ieh6rswg8ixmw0bscv3q9mv