Details

    • 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 ) }...
      

      Attachments

        Activity

          monty Michael Widenius added a comment - - edited

          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,.

          monty Michael Widenius added a comment - - edited 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,.

          How this is to be implemented is a good question, but note that an IDENTITY column doesn't have to be a PRIMARY KEY which means that AUTO_INCREMENT will not work always. There may also be more than one IDENTITY column in a table. This is true both in the specific case of Oracle as in the more generic case of ANSI SQL.

          karlsson Anders Karlsson added a comment - How this is to be implemented is a good question, but note that an IDENTITY column doesn't have to be a PRIMARY KEY which means that AUTO_INCREMENT will not work always. There may also be more than one IDENTITY column in a table. This is true both in the specific case of Oracle as in the more generic case of ANSI SQL.
          nikitamalyavin Nikita Malyavin added a comment - - edited

          It should be relatively straightforward to implement
          GENERATED [ ALWAYS ]AS IDENTITY [ ( identity_options ) ]
          part with following identity_options:

          START WITH integer
          INCREMENT BY integer
          MAXVALUE integer | NOMAXVALUE
          MINVALUE integer | NOMINVALUE
          NOCYCLE, NOORDER, NOCACHE

          I'll estimate this part as 1w.

          CYCLE, CACHE and ORDER is something new to us, and may or may not take extra time.

          I'll reserve 3d for it.

          The BY DEFAULT part
          GENERATED BY DEFAULT [ ON NULL ] AS IDENTITY [ ( identity_options ) ]

          is less predictable, but in average I'd expect it around the same 1w.

          nikitamalyavin Nikita Malyavin added a comment - - edited It should be relatively straightforward to implement GENERATED [ ALWAYS ]AS IDENTITY [ ( identity_options ) ] part with following identity_options: START WITH integer INCREMENT BY integer MAXVALUE integer | NOMAXVALUE MINVALUE integer | NOMINVALUE NOCYCLE, NOORDER, NOCACHE I'll estimate this part as 1w. CYCLE, CACHE and ORDER is something new to us, and may or may not take extra time. I'll reserve 3d for it. The BY DEFAULT part GENERATED BY DEFAULT [ ON NULL ] AS IDENTITY [ ( identity_options ) ] is less predictable, but in average I'd expect it around the same 1w.

          People

            nikitamalyavin Nikita Malyavin
            karlsson Anders Karlsson
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.