Details
-
New Feature
-
Status: In Progress (View Workflow)
-
Critical
-
Resolution: Unresolved
-
Server 12.1 dev sprint
Description
The IDENTITY column feature as per SQL standard as well as at least Oracle 12 and up and DB2:
CREATE TABLE tab1(c1 INTEGER GENERATED ALWAYS AS IDENTITY, |
c2 INTEGER GENERATED BY DEFAULT AS IDENTITY); |
Oracle supports similar options as a SEQUENCE to an IDENTITY column. An Identity column need not be a primary key, but it can be. With GENERATED ALWAYS is used, the user cannot specify a value, only the generated value is allowed.
Oracle-21 syntax for IDENTITY columns:
identity_clause::= GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ]
|
|
identity_options::= { START WITH ( integer | LIMIT VALUE )
|
| INCREMENT BY integer
|
| ( MAXVALUE integer | NOMAXVALUE )
|
| ( MINVALUE integer | NOMINVALUE )
|
| ( CYCLE | NOCYCLE )
|
| ( CACHE integer | NOCACHE )
|
| ( ORDER | NOORDER ) }...
|
We need to have a better definition of "similar options as a SEQUENCE to an IDENTITY"
Can we for example use UUID_SHORT() as the generated number ?
If yes, then this would be trivial to implement.
Another options would be using auto_increment columns or adding a hidden sequence object to the identity column.
The estimate assumes we can use something like UUID_SHORT() or auto_increment to solve this,.