Details
-
New Feature
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
Q2/2025 Development, Q3/2025 Maintenance, Q4/2025 Server Development
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 ) }...
|
Note from monty:
IDENTITY should parse and ignore all the options. The behavior should be controlled by auto_increment_increment and auto_increment_offset.
Note from serg:
IDENTITY should be implemented as Field::default_value. GENERATED ALWAYS AS IDENTITY is also a default_value (since vcols can only be deterministic), but not user-assignable.
Attachments
Issue Links
- is duplicated by
-
MDEV-29818 Add ability to handle Oracle Identity columns in ORACLE Mode
-
- Open
-
- relates to
-
MCOL-6020 Assertion `table && table->s->sequence' failed on update to default defined as nextval
-
- Closed
-
-
MDEV-31854 Server crash, ASAN errors, assertion failure upon using NEXTVAL as default
-
- Closed
-
- links to